0

I am new to Bigquery and stackoverflow as well. I am dealing with multiple rows of JSON codes in a given column and they are all similar but they have different Id's. I am trying to extract DAYS from them.

Currently I am using the following formula to extract DAYS.

SELECT JSON_QUERY(Column,'$.data.extract.b2ed07ab-8f70-47e1-9550-270a23ec5e37.sections[0].TIME[0].DAYS') FROM DEFAULT_TABLE_LOCATION

This gives me what I want but there are multiple rows of data where the only difference is the ID which I've mentioned above b2ed07ab-8f70-47e1-9550-270a23ec5e37.

Is there a way for me to use something like

SELECT JSON_QUERY(Column,'$.data.extract.????????-????-????-????-????????????.sections[0].TIME[0].DAYS') FROM DEFAULT_TABLE_LOCATION

to get the same data stored in different rows?

In summary, Is it possible for me to have a common JSON path to extract values stored in multiple rows, given that they have the same char length to find the DAYS?

Here's a sample code. I have omitted most of the irrelevant code as it was too big to paste here.

{"data":{"CONFDETAILS":[...Some XYZ code...},"extract":{"b2ed07ab-8f70-47e1-9550-270a23ec5e37":{.......},"entities":null,"sections":[{.......,"TIME":[{"DAYS":[false,false,false,false,false,false,true],"end":"23:59","start":"00:00"},{"DAYS":[true,false,false,false,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,true,false,false,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,true,false,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,false,true,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,false,false,true,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,false,false,false,true,false],"end":"23:59","start":"00:00"}],........}

and to give some more perspective, the data in the following rows look like this. With just the ID being different.

{"data":{"CONFDETAILS":[...Some XYZ code...},"extract":{"e520ab02-6ec1-4fdf-b810-0d1b74fc719c":{.......},"entities":null,"sections":[{.......,"TIME":[{"DAYS":[false,false,false,false,false,false,true],"end":"23:59","start":"00:00"},{"DAYS":[true,false,false,false,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,true,false,false,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,true,false,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,false,true,false,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,false,false,true,false,false],"end":"23:59","start":"00:00"},{"DAYS":[false,false,false,false,false,true,false],"end":"23:59","start":"00:00"}],........}

Gurminder Singh
  • 3
  • 4
  • Can I suggest that you post some sample (public/madeup) data that shows your source table and what you would like as output? Keep it simple, maybe make up a simpler problem that, if solved, you could extrapolate to your own puzzle. – Kolban Oct 18 '22 at 15:30
  • Hi, thanks for a quick reply! Here's the code, I have omitted a bunch of code which was irrelevant, did my best to not loose its framework. Hope it makes sense. – Gurminder Singh Oct 18 '22 at 19:19
  • see answer in https://stackoverflow.com/a/74116564/5221944 as your question looks like "exact" dup of respective question – Mikhail Berlyant Oct 18 '22 at 19:42
  • your question is too broad and generic so it is hard to give you more specific answer. meantime check out approach in https://stackoverflow.com/a/70949910/5221944 . it uses classic JsonPath approach. hope you can adopt it to your use case :o) – Mikhail Berlyant Oct 18 '22 at 20:20
  • Yeah both won't work as I am dealing with the TYPE JSON instead of a TYPE STRING and trying both the formats, I got the error "No support yet for JavaScript input of type JSON". I am still new to this, I guess I'll have to do a lot of digging. However, I know all the keys for which I need to make the JSON path for - is it possible for me have something like an or statement within the paths? like $.data.extract.ID1 || ID2 || ID3.sections[0].TIME[0].DAYS? I've read the bigquery reference manual; seems like they've only got [], ., and $ operators. Really out of luck with flexibility here. :/ – Gurminder Singh Oct 18 '22 at 21:35
  • while JSON type is available - full support for JsonPath is not (hopefully yet). So consider transforming your JSON into STRING and use available options! – Mikhail Berlyant Oct 19 '22 at 00:12
  • Maybe using a function coded in JavaScript but callable from your SQL may help? See ... https://stackoverflow.com/questions/46742020/what-jsonpath-expressions-are-supported-in-bigquery – Kolban Oct 19 '22 at 00:22

0 Answers0