Biq-query: add new key:val in json-CodePudding

How can I add a new key/val pair in an already existing JSON col in bigqyery using SQL (big query flavor).

To something like

CodePudding user response：

BigQuery provides Data Manipulation Language (DML) statements such as the SQL Update statement. See:

and you might have another table with those new key:value pairs to use

in case if you don't really have second table - you can just use CTE like below

with new_key_val as (
  select 1 id, '{"key3":"value3"}' add_json union all
  select 2 id, '{"key14":"value14"}' 
)

So, having above - you can use below approach

select *,
  ( select '{' || string_agg(trim(kv)) || ',' || trim(add_json, '{}') || '}'
    from unnest(split(trim(json_col, '{}'), ',')) kv
  ) adjusted_json
from your_table
left join new_key_val
using(id)

with output