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.

@Serge thank you. I tired your suggestion but i got the following error: Syntax Error: Database: prod-redshift (Amazon Redshift) at or near "select" — Sluna
– Sluna, Commented May 17, 2021 at 22:12

Pavel Slepiankou · Accepted Answer · 2021-05-18 12:59:24Z

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;

Serge · Accepted Answer · 2021-05-17 23:12:17Z

1

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

edited May 17, 2021 at 23:12

answered May 17, 2021 at 22:39

Serge

3,8453 gold badges20 silver badges39 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related