How to parse JSON in Standard SQL BigQuery?

Question

After streaming some json data into BQ, we have a record that looks like:

"{\"Type\": \"Some_type\", \"Identification\": {\"Name\": \"First Last\"}}"

How would I extract the type from this? E.g. I would like to get Some_type.

I tried all possible combinations shown in https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions without success, namely, I thought:

SELECT JSON_EXTRACT_SCALAR(raw_json , "$[\"Type\"]") as parsed_type FROM `table` LIMIT 1000

is what I need. However, I get:

Invalid token in JSONPath at: ["Type"]

Picture of rows preview

Mikhail Berlyant · Accepted Answer · 2019-02-24 19:13:13Z

8

Below example is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, "{\"Type\": \"Some_type\", \"Identification\": {\"Name\": \"First Last\"}}" raw_json UNION ALL
  SELECT 2, '{"Type": "Some_type", "Identification": {"Name": "First Last"}}'
)
SELECT id, JSON_EXTRACT_SCALAR(raw_json , "$.Type") AS parsed_type
FROM `project.dataset.table`

with result

Row id  parsed_type  
1   1   Some_type    
2   2   Some_type

See below update example - take a look at third record which I think mimic your case

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, "{\"Type\": \"Some_type\", \"Identification\": {\"Name\": \"First Last\"}}" raw_json UNION ALL
  SELECT 2, '''{"Type": "Some_type", "Identification": {"Name": "First Last"}}''' UNION ALL
  SELECT 3, '''"{\"Type\": \"


                     null1\"}"
  '''
)
SELECT id, 
  JSON_EXTRACT_SCALAR(REGEXP_REPLACE(raw_json, r'^"|"$', '') , "$.Type") AS parsed_type
FROM `project.dataset.table`

with result

Row id  parsed_type  
1   1   Some_type    
2   2   Some_type    
3   3   null1

Note: I use null1 instead of null so you can easily see that it is not a NULL but rather string null1

edited Feb 24, 2019 at 19:13

answered Feb 24, 2019 at 18:37

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GRS Over a year ago

I know it very odd, but when I do the insert of data like in the answer, it works and parses the correct information. But when the same data is the column, I get nulls back.

Mikhail Berlyant Over a year ago

can you show example of few rows in an image with result pane in Table mode - so we see what exactly data we are dealing with

GRS Over a year ago

I added the image, I'm guessing I need to de-quote it before calling JSON_EXTRACT_SCALAR

Mikhail Berlyant Over a year ago

the picture you just added - is it in JSON preview or Table preview?

GRS Over a year ago

Its a table preview, Json preview is "raw_json": "\"{\\\"Type\\\": \\\"

Collectives™ on Stack Overflow

How to parse JSON in Standard SQL BigQuery?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related