How do I add a 'root element' to a json file

Question

I made a SQL query (build up with CTE's) that results in 6 products with their attributes. The query is just select * from output an this results in the following table, with more columns than shown below:

|row | gender | prod_1| url_1 | prod_2 |  url_2| ...
|  1 |  male  |   nike| www.xy|  adidas| www.ap| ...
|  2 | female |   puma| www.zq|   apple| www.ad| ...

When this table is converted to a JSON it looks like this:

[{
  "gender": "male",
  "product_1": "nike",
  "url_1": "www.xy ",
  "product_2": "puma",
  ...,
  "gender": "female",
  "product_1": "adidas",
  "url_1": "www.xy ",
  "product_2": "apple",
  ...,
}]

However, I want to group the results by gender and make the results look like this:

{
   "male": {
       "product_1": "nike",
       "url_1": "www.xy",
       "product_2": "adidas",
       ...,
   }
   "female": {
       "product_1": "puma",
       "url_1": "www.zq",
       "product_2": "apple",
       ...,
   }
}

There are two root elements where all the different product (+ attributes) fall under. Does anybody know if and how this result is possible (with a query?).

My output now looks like this:

But I want the output to look like this:

Are you trying to aggregate all rows into one big json object ? or is it just enough that each row has it's own json object like **{"male": { "product_1": "nike", "url_1": "www.xy", "product_2": "adidas", ..., }}** ? — Jaytiger, Sep 23 '22 at 07:54
@Jaytiger it would be enough if each row would have its own json object! — Thijs Kalshoven, Sep 23 '22 at 11:01

Jaytiger · Answer 1 · 2022-09-23T14:54:05.393

0

Since a key of JSON object should be a column name in Bigquery, so simple workaround would be using STRUCT like below:

WITH sample_table AS (
  SELECT 'male' gender, 'nike' product_1, 'www.xy' url_1, 'puma' product_2, 'www.xyz' url_2
   UNION ALL
  SELECT 'female' gender, 'adidas' product_1, 'www.xy' url_1, 'apple' product_2, 'www.xyz' url_2
)
SELECT CASE gender
         WHEN 'male' THEN TO_JSON_STRING(STRUCT((SELECT AS STRUCT t.* EXCEPT(gender)) AS male))
         WHEN 'female' THEN TO_JSON_STRING(STRUCT((SELECT AS STRUCT t.* EXCEPT(gender)) AS female))
       END AS json
  FROM sample_table t;

Update

SELECT CASE gender -- actually this CASE is not necessary cause it generates same output without it
         WHEN 'male' THEN (SELECT AS STRUCT t.* EXCEPT(gender))
         WHEN 'female' THEN (SELECT AS STRUCT t.* EXCEPT(gender))
       END AS items
  FROM sample_table t;

edited Sep 23 '22 at 14:54

answered Sep 23 '22 at 11:11

Jaytiger

11,626
2
5
15

Thanks a lot! I am almost there, but the structure is not exactly the same. Do you know how to get to the format in the last picture. (I updated my post) – Thijs Kalshoven Sep 23 '22 at 12:46
Are you looking at the json output in Bigquery console ? – Jaytiger Sep 23 '22 at 13:33
Yes! and the 1st picture is the saved results as a json. The 2nd picture of the desired output json is handmade. – Thijs Kalshoven Sep 23 '22 at 14:25
If you want a json output like your 2nd picture, it should be a struct in bigquery table, not a json string. which one do you want ..? and I can't see the **male** or **female** fields in your 2nd picture. – Jaytiger Sep 23 '22 at 14:27
The "Items" string should be interpreted as the male one, and then a second "items" should be female. So i guess I would like it as a struct in bigquery. Sorry I did not work a lot with SQL and BQ. So the second picture is indeed how its desired – Thijs Kalshoven Sep 23 '22 at 14:46
@ThijsKalshoven, I see. **items** part is still not clear to me, but kindly check the updated output if it is what you want. – Jaytiger Sep 23 '22 at 14:55

How do I add a 'root element' to a json file

1 Answers1

Update