CODEWITHSUNDEEP
sqljsongoogle-bigquery
Jenny
Jenny

Reputation: 3

BigQuery - JSON_EXTRACT only extracts first entry

I have a column containing a json-string as follows:

[{"answer":"europe-austria-swiss","text":"Österreich, Schweiz"},{"answer":"europe-italy","text":"Italien"},{"answer":"europe-france","text":"Frankreich"}]

I want to extract ALL answers given in ONE column and row, seperated by a comma:

europe-austria-swiss, europe-italy, europe-france

I think I tried all possibilites offered by JSON_EXTRACT and JSON_EXTRACT_ARRAY or replacing parentheses and other signs, but I either only get the first entry extracted (in this case

europe-austria-swiss

) or it splits up in rows as array from which I can no longer extract the strings of "answer".

Has anyone any idea on how to solve that problem? It's very much appreciated! This column is of course part of a much larger table (if that is relevant anyhow).

Upvotes: 0

Views: 2808

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Below is for BigQuery Standard SQL

#standardSQL
SELECT (
  SELECT STRING_AGG(JSON_EXTRACT_SCALAR(answer, '$.answer'), ' ,') 
  FROM UNNEST(JSON_EXTRACT_ARRAY(json_string)) answer
  ) AS answers 
FROM `project.dataset.table`

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '[{"answer":"europe-austria-swiss","text":"Österreich, Schweiz"},{"answer":"europe-italy","text":"Italien"},{"answer":"europe-france","text":"Frankreich"}]' json_string
)
SELECT (
  SELECT STRING_AGG(JSON_EXTRACT_SCALAR(answer, '$.answer'), ' ,') 
  FROM UNNEST(JSON_EXTRACT_ARRAY(json_string)) answer
  ) AS answers 
FROM `project.dataset.table`

with result

Row answers
1   europe-austria-swiss ,europe-italy ,europe-france

Upvotes: 0

Alvaro
Alvaro

Reputation: 963

I think I know what's going on (please, correct me if I'm wrong).

My best guess is that you are trying something like:

SELECT JSON_EXTRACT(json_text, "$.answer") AS answers
FROM UNNEST([
'{"answer":"europe-austria-swiss","text":"Österreich, Schweiz"},{"answer":"europe-italy","text":"Italien"},{"answer":"europe-france","text":"Frankreich"}'
]) as json_text

This returns:

"europe-austria-swiss"

However, if you change the underlying data for something like this (each line as a json string object), it should resolve the issue:

SELECT JSON_EXTRACT(json_text, "$.answer") AS answers
FROM UNNEST([
'{"answer":"europe-austria-swiss","text":"Österreich, Schweiz"}',
'{"answer":"europe-italy","text":"Italien"}',
'{"answer":"europe-france","text":"Frankreich"}'
]) as json_text

Result:

"europe-austria-swiss"
"europe-italy"
"europe-france"

Hope this helps!

Upvotes: 1

Related Questions