BigQuery - convert nested column into json without column name-CodePudding

i have the following table schema:

i want to convert the items column into a json string but the output of TO_JSON_STRING isn't what i need. when i run this query i get:

SELECT id, store, TO_JSON_STRING(items) AS items_json
FROM nested_array_example

but what i need is items_json to be:

{"table": "3", "lamp": "7", "swedish_thing": "729"}

is there a way to do that in bigquery? here is the query to generate the table's data:

INSERT INTO `project_name.data_seT_name.nested_array_example` (store, items, id)
VALUES ("ikea", [("table","3"),("lamp","7"),("swedish_thing",'729'),("swedish_thing_made_in_china",'5723')], '1')

CodePudding user response：

Consider below approach

select id, store, 
  ( select '{' || string_agg(format('"%s": "%s"', name, value)) || '}' 
    from t.items
  ) items_json
from `project_name.data_seT_name.nested_array_example` t

if applied to sample data from your question - output is