select a json key's value using sql in bigQuery

Question

I run a simple query that returns an output like this:

[{
  "polarity": "0.0",
  "magnitude": "2.0",
  "score": "0.5",
  "entities": [{
    "name": "Taubenkot",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.150263"
  }, {
    "name": "Lösung",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.145794"
  }, {
    "name": "Busbahnhof",
    "type": "LOCATION",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.108006"
  }, {
    "name": "Stadt",
    "type": "LOCATION",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "3",
    "avgSalience": "0.079928"
  }, {
    "name": "Taubenplage",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "2",
    "avgSalience": "0.072326"
  }, {
    "name": "Rutschgefahr",
    "type": "OTHER",
    "mid": "",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.070839"
  },
{
    "name": "Meike Böschemeyer",
    "type": "PERSON",
    "mid": "/g/11btlnnjgf",
    "wikipediaUrl": "",
    "numMentions": "1",
    "avgSalience": "0.00451"
  }]
}]

BigQuery automatically shows the different entity key values in separate columns. How can I query a particular key's value?

I was trying this:

select JSON_VALUE(entities, '$.type') AS type from gcnlapi limit 1

but it gives me this error

No matching signature for function JSON_VALUE for argument types: ARRAY>, STRING. Supported signatures: JSON_VALUE(STRING, [STRING]); JSON_VALUE(JSON, [STRING]) at [3:8]

Also tried this:

select entities.type AS type from gcnlapi limit 1

but that would give me

Cannot access field type on a value with type ARRAY> at [5:17]

Mazlum Tosun · Answer

I hope this example can help you :

SELECT
    polarity,
    magnitude,
    score,

    name,
    type,
    mid,
    wikipediaUrl,
    numMentions,
    avgSalience
FROM
    `your_project.your_dataset.your_table`,
    UNNEST(entities)

With UNNEST you can flatten your array and get the field at root level of the table and also the fields of the array (flattened).

In this query, fields at root level are polarity, magnitude and score and others correspond to the array fields.

select a json key's value using sql in bigQuery

1 Answers