how to read multiple levels of JSON data in Big Query using JSON_EXTRACT or JSON_EXTRACT_SCALAR

Question

I am trying to read the below JSON structure in Bigquery using JSON_EXTRACT in Bigquery ..it says unsupported operator Path "*"

Tried all the ways I can in BigQuery and Request your help

Error: Unsupported operator in JSONPath: *

****JSON data:** JUST THE PORTION that has multiple values and which has Issues while reading . Need to read all 4 "id" values below as an e.g. and need to read all other columns as well under Combo section which produces 4 rows with different ID,Type etc.**

"Combos": [
  {
    "Id": "1111",
    "Type": 0,
    "Description": "ABCD",
    "ComboDuration": {
      "StartDate": "2009-10-26T08:00:00",
      "EndDate": "2009-10-29T08:00:00"
    }
  },
  {
    "Id": "2222",
    "Type": 1,
    "Description": "XYZ",
    "ComboDuration": {
      "StartDate": "2019-10-26T08:00:00",
      "EndDate": "2019-10-29T08:00:00"
    }
  },
  {
    "Id": "39933",
    "Type": 3,
    "Description": "General",
    "ComboDuration": {
      "StartDate": "2019-10-26T08:00:00",
      "EndDate": "2019-10-29T08:00:00"
    }
  },
  {
    "Id": "39934",
    "Type": 2,
    "Description": "ABCDXYZ",
    "ComboDuration": {
      "StartDate": "2019-10-26T08:00:00",
      "EndDate": "2019-10-29T08:00:00"
    }
  },

]

****Code:** P.S - conv_column is a string column where my JSON structure stored**

SELECT 
JSON_EXTRACT(conv_column,"$.Combos.*.Id") as combo_id
from lz.json_file

SELECT JSON_EXTRACT(conv_column,"$.Combos[*].Id") as combo_id
from lz.json_file

SELECT JSON_EXTRACT(conv_column,"$.Combos[?@.Id]") as combo_id
from lz.json_file

`@Nanda` - note: such "targeted" messages are sent to "recipient" ONLY if he/she is already part of given post - otherwise - it is just ignored - meaning not being delivered to expected "target" — Mikhail Berlyant, Jan 22 '20 at 05:18
@MikhailBerlyant - I wasnt aware of how the comments work here, thanks a lot for all your help and support — Nanda, Jan 22 '20 at 14:37

score 2 · Accepted Answer · answered Jan 22 '20 at 05:14

2

Below example BigQuery for Standard SQL

#standardSQL
CREATE TEMP FUNCTION jsonparse(input STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(input).map(x=>JSON.stringify(x));
"""; 
WITH `project.lz.json_file` AS (
  SELECT '''{
  "Combos": [  {
    "Id": "1111",
    "Type": 0,
    "Description": "ABCD",
    "ComboDuration": {
      "StartDate": "2009-10-26T08:00:00",
      "EndDate": "2009-10-29T08:00:00"
    }  },  {
    "Id": "2222",
    "Type": 1,
    "Description": "XYZ",
    "ComboDuration": {
      "StartDate": "2019-10-26T08:00:00",
      "EndDate": "2019-10-29T08:00:00"
    }  },  {
    "Id": "39933",
    "Type": 3,
    "Description": "General",
    "ComboDuration": {
      "StartDate": "2019-10-26T08:00:00",
      "EndDate": "2019-10-29T08:00:00"
    }  },  {
    "Id": "39934",
    "Type": 2,
    "Description": "ABCDXYZ",
    "ComboDuration": {
      "StartDate": "2019-10-26T08:00:00",
      "EndDate": "2019-10-29T08:00:00"
    }  }]}  ''' AS conv_column
)
SELECT
  JSON_EXTRACT_SCALAR(combo, '$.Id') AS Id,
  JSON_EXTRACT_SCALAR(combo, '$.Type') AS Type,
  JSON_EXTRACT_SCALAR(combo, '$.Description') AS Description,
  JSON_EXTRACT_SCALAR(combo, '$.ComboDuration.StartDate') AS StartDate,
  JSON_EXTRACT_SCALAR(combo, '$.ComboDuration.EndDate') AS EndDate
FROM `project.lz.json_file`,
UNNEST(jsonparse(JSON_EXTRACT(conv_column, '$.Combos'))) combo

with output

Row Id      Type    Description StartDate           EndDate  
1   1111    0       ABCD        2009-10-26T08:00:00 2009-10-29T08:00:00  
2   2222    1       XYZ         2019-10-26T08:00:00 2019-10-29T08:00:00  
3   39933   3       General     2019-10-26T08:00:00 2019-10-29T08:00:00  
4   39934   2       ABCDXYZ     2019-10-26T08:00:00 2019-10-29T08:00:00

answered Jan 22 '20 at 05:14

Mikhail Berlyant

165,386
8
154
230

Thanks a lot for the help. When I tried replace the "Combos" in the WITH statement with the actual column "conv_column" - variable which is a string and has JSON structure I get the "TypeError: Cannot read property 'map' of null at jsonparse(STRING) line 2, columns 26-27". Code snippet I am using is below along, :CREATE TEMP FUNCTION jsonparse(input STRING) RETURNS ARRAY LANGUAGE js AS """ return JSON.parse(input).map(x=>JSON.stringify(x)); """; WITH `lz.json_file` AS ( SELECT job_id,conv_column from lz.json_file ) followed by statements you coded – Nanda Jan 22 '20 at 14:07
`conv_column` is already used as a column name - the column that consist of your json string. play with the example provided and adjust it to whatever actually you have there :o) – Mikhail Berlyant Jan 22 '20 at 14:10
- Sure. I am trying below still get same error:CREATE TEMP FUNCTION jsonparse(input STRING) RETURNS ARRAY LANGUAGE js AS """ return JSON.parse(input).map(x=>JSON.stringify(x)); """; SELECT JSON_EXTRACT_SCALAR(combo, '$.Id') AS Id FROM `lz.json_file`, UNNEST(jsonparse(JSON_EXTRACT(conv_column, '$.Combos'))) combo – Nanda Jan 22 '20 at 14:24
have you tried example in my answer? are you sure that your real json text in that column is as in your question and my answer - or it is (looks like) something else / different which would explain your problem – Mikhail Berlyant Jan 22 '20 at 18:21
thanks a lot for the help so far. I took sometime this morning to figure out whats happening with the Input data and Issue was with the Incomplete JSON Structure and I have fixed the same. It works now completely fine with all the test cases i worked on and it extracts the data as expected. – Nanda Jan 22 '20 at 22:45
great! glad you nail it! feel free then to vote up and accept the answer :o) – Mikhail Berlyant Jan 22 '20 at 22:47

how to read multiple levels of JSON data in Big Query using JSON_EXTRACT or JSON_EXTRACT_SCALAR

1 Answers1