How to get value from a JSON array column using Redshift?

Question

Background

I have a table and one of the column contains data that looks like this:

[{"category": "Sports & Outdoors", "rank": 218, "link": "www.foop.com"},{"category": "Bike Saddle Covers", "rank": 1, "link" : "www.foo2.com"}]

From what I understand, the above is a json array. I tried select json_array_length(col_1) from mytable and was able to get back a length of 2 so I know it is a json array.

Issue

I am looking to extract value for the key category from each json inside the array. I am unsure how to proceed. I know that if it was a simple json I can do something like select col_name -> 'category' from table.

What I tried

`select array_to_json(col_1) from mytable`

Error: function array_to_json(character varying) does not exist

I also tried select array_to_json(col_1::varchar) from mytable

I would appreciate any help here as i am very new to sql and have only done basic querying.

user_798335 · Answer

Despite Redshift is based on Postgres it's different in some parts.

JSON functions are very limited in redshift. The only possible solution for your task with these functions which I can imagine is:

select
    json_extract_path_text(
        json_extract_array_element_text(col_1, 0),
        'category')
from mutable
union
select
    json_extract_path_text(
        json_extract_array_element_text(col_1, 1),
        'category')
from mutable;

which results in

That for sure is not very scalable solution if you have a complex JSON structure or many elements inside a JSON array.

using null_if_invalid param can help for some extent

select
    json_extract_path_text(
        json_extract_array_element_text(col_1, 0),
        'category'
    )
from mutable

union
select
    json_extract_path_text(
        json_extract_array_element_text(col_1, 1, true),
        'category', true)
from mutable

union
select
    json_extract_path_text(
        json_extract_array_element_text(col_1, 2, true),
        'category', true)
from mutable

union
select
    json_extract_path_text(
        json_extract_array_element_text(col_1, 3, true),
        'category', true)
from mutable;

user_5874981 · Answer

You build a join of the table and json array

SELECT arr.item ->> 'category'
FROM myTable, json_array_elements(col1) arr(item)

Here arr(item) is arbitrary notation that allow us to reference individual elements of the json array. Here is tutorial for that case

https://levelup.gitconnected.com/working-with-a-jsonb-array-of-objects-in-postgresql-d2b7e7f4db87

How to get value from a JSON array column using Redshift?

2 Answers