Skip to content

Tag: parsing

Generate rows from JSON (SDO_GEOMETRY)

I have a multi-part SDO_GEOMETRY that I can represent as JSON (Oracle 21c): As an experiment, I want to find a way to generate rows from the JSON: Each row is a vertex of the geometry. Question: In an SQL query, is there a way to generate rows for each of the JSON vertices? I’m aware that SDO_GEOMETRY has a

JSON column parse in SQL

one of the columns in my database contains an imported JSON file. Format of JSON: There are hundreds of objects with taskId (3271012,3261201,…) how can i parse this tasks from one column to multiple rows? expected output: Answer First you can get all the “data” items as key-value pairs with OPENJSON and then extract the values of interest The same

Using SSIS OR T-SQL Split a column of quoted & unquoted comma separated values into multiple columns

I have comma separated data in a column named C0. The data in C0 looks like this: C0 “Pacey LLC.”,213830ZZ,11/1/2017,11/1/2017,”297,311.74″,”2,371.40″,0.00,”1,325.18″,0.00,42.22,”123,986.56″ Mike The Miker,9814140VCD,12/1/2018,12/1/2018,”3,917,751.99″,”419,743.54″,”36,642.66″,”344,090.43″,0.00,10.00,”2,434,671.06″ And I want it to end up like this: F1 F1 F3 F4 F5 F6 F7 F8 F9 F10 F11 “Pacey LLC.” 213830ZZ 11/1/2017 11/1/2017 297,311.74 2,371.40 0.00 1,325.18 0.00 42.22 123,986.56 Mike The Miker 9814140VCD 12/1/2018

fetch the data from array of objects sql BigQuery

I need to fetch key value pairs from the second object in array. Also, need to create new columns with the fetched data. I am only interested in the second object, some arrays have 3 objects, some have 4 etc. The data looks like this: The desired output: Answer Below is for BigQuery Standard SQL if applied to sample data