CODEWITHSUNDEEP
jsongoogle-bigquery
pdolinaj
pdolinaj

Reputation: 1145

BigQuery - Query JSON

I have JSON stored in this kind of format:

[{
    "name": "total_video_views",
    "values": [{
        "value": 3720
    }]
}, {
    "name": "total_video_views_unique",
    "values": [{
        "value": 3648
    }]
}]

The JSON file has more rows with many different metrics.

How can I query this for a specific metric?

(for example give me value for: name = total_video_views_unique)

I can query this when I know at which index is the value:

SELECT JSON_EXTRACT(<MY_JSON_STRING>, '$[1].name'), JSON_EXTRACT(<MY_JSON_STRING>, '$[1].values[0].value')

Thank you.

Upvotes: 1

Views: 1750

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

To overcome BigQuery "limitation" for JsonPath, you can use custom function as below example shows:
It uses jsonpath-0.8.0.js that can be downloaded from https://code.google.com/archive/p/jsonpath/downloads and uploaded to Google Cloud Storage - gs://your_bucket/jsonpath-0.8.0.js 

#standardSQL
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS STRING
LANGUAGE js AS """
    try { var parsed = JSON.parse(json);
        return JSON.stringify(jsonPath(parsed, json_path));
    } catch (e) { return null }
"""
OPTIONS (
    library="gs://your_bucket/jsonpath-0.8.0.js"
);
WITH t AS (
SELECT '''
{ "store": {
        "book": [ 
            { "category": "reference",
                "author": "Nigel Rees",
                "title": "Sayings of the Century",
                "price": 8.95
            },
            { "category": "fiction",
                "author": "Evelyn Waugh",
                "title": "Sword of Honour",
                "price": 12.99
            },
            { "category": "fiction",
                "author": "Herman Melville",
                "title": "Moby Dick",
                "isbn": "0-553-21311-3",
                "price": 8.99
            },
            { "category": "fiction",
                "author": "J. R. R. Tolkien",
                "title": "The Lord of the Rings",
                "isbn": "0-395-19395-8",
                "price": 22.99
            }
        ],
        "bicycle": {
            "color": "red",
            "price": 19.95
        }
    }
}
''' AS x
)
SELECT 
    CUSTOM_JSON_EXTRACT(x, '$.store.book[*].author'),
    CUSTOM_JSON_EXTRACT(x, '$..*[?(@.price==22.99)].author'),
    CUSTOM_JSON_EXTRACT(x, '$..author'),
    CUSTOM_JSON_EXTRACT(x, '$.store.*'),
    CUSTOM_JSON_EXTRACT(x, '$.store..price'),
    CUSTOM_JSON_EXTRACT(x, '$..book[(@.length-1)]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[-1:]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[0,1]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[:2]'),
    CUSTOM_JSON_EXTRACT(x, '$..book[?(@.isbn)]')
FROM t

Result is as below

For CUSTOM_JSON_EXTRACT(x, '$.store.book[*].author') 

[
  "Nigel Rees"
  "Evelyn Waugh"
  "Herman Melville"
  "J. R. R. Tolkien"
]

For CUSTOM_JSON_EXTRACT(x, '$..*[?(@.price==22.99)].author') 

[
  "J. R. R. Tolkien"
]

For CUSTOM_JSON_EXTRACT(x, '$.store..price') 

[
  8.95
  12.99
  8.99
  22.99
  19.95
]

Upvotes: 2

Philipp Sh
Philipp Sh

Reputation: 997

Unfortunately I did not understand your question clearly enough. Generally, there are two answers to this type of problem from my understanding.

If you have a JSON file that you want to query with BigQuery, you can just import the file to BigQuery and query it normally.

If you mean that you have a JSON structure stored as a string in a column, then it is impossible to get the desired node dynamically only using the SQL query, because you will need to look through the JSON object with a scripting or programming language.

Upvotes: 0

Related Questions