CODEWITHSUNDEEP
google-bigquery
Angelo
Angelo

Reputation: 3

BigQuery JSON EXTRACT

[
   {
      "key":"expiry_date",
      "type":"date",
      "label":"Expiry Date",
      "required":false,
      "default_value":"2029-12-15"
   },
   {
      "key":"brand",
      "type":"text",
      "label":"Brand",
      "required":false,
      "default_value":"clipsal"
   }
]

Is there a way that I could extract the default_value of "expiry_date" in the nested JSON above? The data is under a column called attributes.

Upvotes: 0

Views: 986

Answers (2)

Airton Gessner
Airton Gessner

Reputation: 215

Have you tried any of these functions described here? Maybe it can help.

Also, if the first element of the json array will be always what you want, you could use something like:

WITH test_table AS (
  SELECT "[{\"key\":\"expiry_date\",\"type\":\"date\",\"label\":\"Expiry Date\",\"required\":false,\"default_value\":\"2029-12-15\"},{\"key\":\"brand\",\"type\":\"text\",\"label\":\"Brand\",\"required\":false,\"default_value\":\"clipsal\"}]" AS json_text_field
)

SELECT JSON_EXTRACT(json_text_field, '$[0].default_value') FROM test_table

If the keys it's not always the first, you could use this instead:

WITH test_table AS (
  SELECT "[{\"key\":\"expiry_date\",\"type\":\"date\",\"label\":\"Expiry Date\",\"required\":false,\"default_value\":\"2029-12-15\"},{\"key\":\"brand\",\"type\":\"text\",\"label\":\"Brand\",\"required\":false,\"default_value\":\"clipsal\"}]" AS json_text_field
)

SELECT value FROM (
  SELECT JSON_EXTRACT(json_text_field, '$.key') AS id, JSON_EXTRACT(json_text_field, '$.default_value') AS value FROM test_table, UNNEST(JSON_EXTRACT_ARRAY(json_text_field, '$')) AS json_value
) WHERE id = '"expiry_date"'

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173181

Below is for BigQuery Standard SQL 

#standardSQL
SELECT JSON_EXTRACT_SCALAR(el, '$.default_value') AS default_value 
FROM `project.dataset.table`,
UNNEST(JSON_EXTRACT_ARRAY(json)) el
WHERE JSON_EXTRACT_SCALAR(el, '$.key') = 'expiry_date'

You can test above with sample / dummy data from y our question as in below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '''
[
   {
      "key":"expiry_date",
      "type":"date",
      "label":"Expiry Date",
      "required":false,
      "default_value":"2029-12-15"
   },
   {
      "key":"brand",
      "type":"text",
      "label":"Brand",
      "required":false,
      "default_value":"clipsal"
   }
]  
  ''' json
)
SELECT JSON_EXTRACT_SCALAR(el, '$.default_value') AS default_value 
FROM `project.dataset.table`,
UNNEST(JSON_EXTRACT_ARRAY(json)) el
WHERE JSON_EXTRACT_SCALAR(el, '$.key') = 'expiry_date'

with output 

Row default_value    
1   2029-12-15

Depends on your real use case - you can consider below variation 

#standardSQL
SELECT *,
  (
    SELECT JSON_EXTRACT_SCALAR(el, '$.default_value') 
    FROM UNNEST(JSON_EXTRACT_ARRAY(json)) el
    WHERE JSON_EXTRACT_SCALAR(el, '$.key') = 'expiry_date'
  ) AS default_value
FROM `project.dataset.table`

Upvotes: 0

Related Questions