CODEWITHSUNDEEP
sqljsongoogle-bigquerybigquery-udf
Aviad Amar
Aviad Amar

Reputation: 47

Key value table to json in BigQuery

Hey all,

I have a table that looks like this:

row key val
1 a 100
2 b 200
3 c "apple
4 d {}

I want to convert it into JSON:

{
    "a": 100,
    "b": 200,
    "c": "apple",
    "d": {}
}

Note: the number of lines can change so this is only an example

Thx in advanced !

Upvotes: 0

Views: 446

Answers (1)

Jaytiger
Jaytiger

Reputation: 12264

With string manipulation,

WITH sample_table AS (
  SELECT 'a' key, '100' value UNION ALL
  SELECT 'b', '200' UNION ALL
  SELECT 'c', '"apple"' UNION ALL
  SELECT 'd', '{}'
)
SELECT '{' || STRING_AGG(FORMAT('"%s": %s', key, value)) || '}' json
  FROM sample_table;

You can get following result similar to your expected output.

enter image description here

Upvotes: 1

Related Questions