Find all values with some key located in separate property using JSONPath

user_9409330 0
Viewed 244

My customer stores data in GCP BigQuery service as table with column named people with type string containing JSON data

One part of data look like:

{
  "id": "someid",
  "data": [
    {
      "key": "country",
      "valueString": "USA"
    },
    {
      "key": "name",
      "valueString": "Peter"
    }
  ]
}

The other one:

{
  "id": "someid",
  "data": [
    {
      "key": "age",
      "valueString": "23"
    },
    {
      "key": "country",
      "valueString": "France"
    },
    {
      "key": "name",
      "valueString": "Peter"
    }
  ]
}

I want to create a query that can find all values where key="country":

country|
-------|
USA    |
-------|
France |
-------|
USA    |
-------|
.......|

It seems to me that I need to use the JSONPath query language. My initial version is:

SELECT JSON_EXTRACT_SCALAR(people, "$.data[0].valueString") AS country
FROM table_name

But it doesn't solve the problem, because this field can store in 0, 1,... n elements.

jsonpath google-bigquery json
3 Answers

I don't know BigQuery, but if it takes standard JSONPath, then try

SELECT JSON_EXTRACT_SCALAR(people, "$.data[?(@.key == 'country')].valueString") AS country FROM table_name

That should give you the valueString for each data item with key == "country".

https://github.com/json-path/JsonPath#path-examples

user_23398250

Consider below [super]simple approach

select json_extract_scalar(json, '$.valueString') as country
from your_table, unnest(json_extract_array(people, '$.data')) json
where json_extract_scalar(json, '$.key') = 'country'

if applied to sample data in your question - output is

enter image description here

user_52219440

You can use a json_query_array and unnest to achive this

with j1 as (
  select  1 id, '{ "id": "someid",  "data": [    {      "key": "country",      "valueString": "USA"    },    {      "key": "name",      "valueString": "Peter"    }  ]}' as j
  union all 
  select 2 ,'{  "id": "someid",  "data": [    {      "key": "age",      "valueString": "23"    },    {      "key": "country",      "valueString": "Germany"    },    {      "key": "name",      "valueString": "Peter"    }  ]}'
),
j2 as (
    select 
    j1.id,
    JSON_EXTRACT_SCALAR(jj,'$.key') key, 
    JSON_EXTRACT_SCALAR(jj,'$.valueString') value 
    from j1,unnest(json_query_array(j, '$.data')) as jj
)
select id,value country from j2 where key='country'
user_23084730
Related