Map data with fields value

user_19264018 0
Viewed 60

Ive a data set in below format and trying below big query

enter image description here

Some additional content, Key & value are array format.

Table format: id, {key: "abc", value: "true"} and when I unnest it looks as in above screenshot.

I want to run a SQL query based on key & value : ex, I want to get only those value if abc = false, def = true

Sample output I'm expecting.

enter image description here

I'm not sure if I'm making any sense. but this is what I'm trying to achieve.

Thanks!

google-bigquery sql
1 Answers

With the data you shared you could use a CASE expression. The Query below should work fine for you:

SELECT
 id,
 key,
 CASE key
   WHEN 'abc' THEN 'FALSE'
   WHEN 'def' THEN 'TRUE'
   ELSE NULL
   END
   AS VALUE
FROM `project.dataset.table`
WHERE key='abc' or key='def'

enter image description here

user_169293580
Related