CODEWITHSUNDEEP
sqlgoogle-bigquery
lukechambers91
lukechambers91

Reputation: 711

Return aggregated JSON from BigQuery?

If I have the below example_table in BigQuery. When I query the table with "Original Query" I get the "Actual Result" (which makes since). Is there a way to query BigQuery directly to get the "Desired Result"

Original Query

SELECT ID, SUBID FROM `example_table ORDER BY ID

example_table

ID | SUBID
12   abc
12   def
12   ghi
34   jkl
34   mno
56   prg

Actual Result

[{
    "ID": "12",
    "SUBID": "abc"
}, {
    "ID": "12",
    "SUBID": "def"
}, {
    "ID": "12",
    "SUBID": "ghi"
}, {
    "ID": "34",
    "SUBID": "jkl"
}, {
    "ID": "34",
    "SUBID": "mno"
}, {
        "ID": "56",
        "SUBID": "prg"
    }]

Desired Result

[{
    "ID": "12",
    "SUBID": ["abc", "def", "ghi"]
}, {
    "ID": "34",
    "SUBID": ["jkl", "mno"]
}, {
        "ID": "56",
        "SUBID": ["prg"]
    }]

Upvotes: 1

Views: 5604

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Below is for BigQuery Standard SQL 

#standardSQL
SELECT ID, ARRAY_AGG(SUBID) SUBID
FROM `project.dataset.example_table`
GROUP BY ID

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

#standardSQL
WITH `project.dataset.example_table` AS (
  SELECT 12 ID, 'abc' SUBID UNION ALL
  SELECT 12, 'def' UNION ALL
  SELECT 12, 'ghi' UNION ALL
  SELECT 34, 'jkl' UNION ALL
  SELECT 34, 'mno' UNION ALL
  SELECT 56, 'prg' 
)
SELECT ID, ARRAY_AGG(SUBID) SUBID
FROM `project.dataset.example_table`
GROUP BY ID
-- ORDER BY ID

with result

enter image description here

Upvotes: 5

likwidfire2k
likwidfire2k

Reputation: 195

If BigQuery does use MySQL syntax you might be able to do this. If not you can continue CONCAT throughout all of your query using multiple selects but it would be a little more convoluted instead of the JSON_ARRAYAGG. 

SELECT CONCAT('{','ID:', ID,', SUBID:', JSON_ARRAYAGG(SUBID),'}') as JSON
FROM contact GROUP BY ID;

https://www.db-fiddle.com/f/37ru5oq4dFQSscwYsfx386/25

Upvotes: 0

Related Questions