I have two tables in Big Query as shown: table_1 has id, properties in json format, collection columns repectively.

table_2 has collection, property_key, property_name columns

I want to replace the json keys in properties column from table_1 with property_name from table_2

condition: where tabe_1.collection = table_2.collection and also property_key in table_2 contains the actual json_key's

Table_1:

Table_2:

I wanted the result table be like a new column with changed properties field from table_1 as shown

Result_table:

Can someone guide me the correct way to solve this in BigQuery using sql or sqludf's

CodePudding user response：

Consider below approach

select id, collection, 
  '{' || string_agg(renamed_property, ',' order by offset) || '}' renamed_properties
from (
  select id, collection, property, offset, 
    array_agg(replace(property, property_key, property_name) order by regexp_contains(property, property_key) desc limit 1)[offset(0)] renamed_property
  from table_1 t1,
  unnest(regexp_extract_all(properties, r'"[^"] ":{"type":"[^"] ","value":[^}] }')) as property with offset 
  left join table_2 t2
  using (collection) 
  group by id, collection, property, offset
)
group by id, collection    

if applied to sample data in y our question - output is