BigQuery extract fields from unnested json

Question

I am able to extract an array from a json string. But I cannot get a subquery to work to extract the values from the at array.

The current query goes like this:

SELECT 
 JSON_QUERY_ARRAY(dv,'$.items')       as items
from  mytable t
,unnest(JSON_QUERY_ARRAY(t.jsonish_field))as dv
  WHERE  t.type='adjustment';

This results in the following rows

items
{"amount":0.93,"status":"COMPLETED","code":"SMBL"}
{"amount":9,"status":"COMPLETED","code":"BDES"}

I would like to be able to expand items into the columns: amount, status, code

I have tried using subqueries but I am unable to reference the items fields.

Answer 1

Use below

select 
  json_value(items, '$.amount') as amount,
  json_value(items, '$.status') as status,
  json_value(items, '$.code') as code
from result    

with output