JSON query/extract certain values based on key?

Question

I have a 'metafields' column that looks like this:

 [{"key":"sport","value":"football","namespace":"global"},{"key":"event","value":"super bowl","namespace":"global"},{"key":"game","value":"3","namespace":"global"}]

The field can have an arbitrary number of objects/keys per row, it's unlimited.

Table has following columns: owner_id (STR),owner_type (STR),metafields (JSON, shown above)

What I'd like to do is query for a specific value based on key, i.e.

SELECT 
owner_id, 
owner_type, 
JSON_QUERY(metafields,"PATH") AS sport, 
JSON_QUERY(metafields,"PATH") AS event, 
JSON_QUERY(metafields,"PATH") AS game
FROM 
table  

Per above example, the output would look something like this:

However, I'm having trouble determining what the "PATH" should be for this type of query, or if JSON_QUERY is even the right function for this use case?

Answer 1

Consider below approach

select * from (
  select owner_id, owner_type,
    json_value(metafield, '$.key') as key,
    json_value(metafield, '$.value') as value
  from your_table, unnest(json_extract_array(metafields)) metafield
)
pivot (any_value(value) for key in ('sport', 'event', 'game'))    

if applied to dummy data like in your question - output is