Extract value based on specific key from array of jsons in Amazon Redshift

Question

I have a table in Redshift say T1, with column name as col1 datatype as varchar. However data is stored in col1 is json data.

col1 value : [{"id":1,"value":null},{"id":2,"value":null},{"id":3,"value":"https://www.google.co.in"},{"id":4,"value":"India"}]

Now i want to extract data based on the key, i.e. if i provide value of id in where clause it should fetch me value corresponding to that id

I have tried below query:

select json_extract_array_element_text(col1, 0) as json_value,
json_extract_path_text(json_value, 'value') as value 
from T1

I'm not able to find a way to filter based on id in the above query.

I would appreciate if anyone could help me with that.

Answer 1

You need to cross join your data with the json array with a list of integers long enough to extract all the array elements. When you have the rows "exploded" out you can extract the "id" and "value" for each and apply the desired WHERE clause then.

There are a number of examples of doing this in SO but if you need further help reach out in a comment.

=========================================

A demo SQL of the step in breaking json arrays and elements apart into database rows and columns. I've made each step a CTE to hopefully improve clarity. It should also be noted that performing queries on large amounts of native json data can be very slow and converting often queried data to distinct columns is advised. Also, the code as written will only expand 10 elements of the json array - if more elements than this are needed then the numbers CTE will need more values.

with recursive numbers(n) as  -- this creates the numbers 0 - 9
( select 0 as n
    union all
    select n + 1
    from numbers n
    where n.n < 9
),
inputtext as   -- put your input string into a CTE; you may have a table for this
( select '[{"id":1,"value":null},{"id":2,"value":null},{"id":3,"value":"https://www.google.co.in"},{"id":4,"value":"India"}]'::text as col1
),
exploded as   -- cross join the input data with the numbers to extract all the array elements
( select json_extract_array_element_text(col1, n) as json_value
from inputtext cross join numbers
),
smashed as         -- extract the json elements id and value
( select json_extract_path_text(json_value, 'id') as id, json_extract_path_text(json_value, 'value') as value 
from exploded
where json_value is not null
)
select id, value -- select the id & value of interest
from smashed
where id = 3
;