how to extract a value from json string

Question

I have a source table as like below

on top this table there is a business logic implemented as like below

select id, TO_JSON_STRING(ARRAY_AGG(STRUCT(name,salary,deptno))) as d_list 
from `project.dataset.sample_tab` 
group by 1

Now after applying business logic the table look like below

From the above table i want to extract only salary column data for additional calculations. how we can extract ? please suggest me

I tried JSON_EXTRACT(d_list,"$.salary") but it is giving me null values

Answer 1

Use below

select id, json_extract(trim(d_list, '[]'),"$.salary") salary
from (
  select id, TO_JSON_STRING(ARRAY_AGG(STRUCT(name,salary,deptno))) as d_list 
  from `project.dataset.sample_tab` 
  group by 1
)             

if applied to sample data in your question - output is