Redshift Postgresql - How to Parse Nested JSON

Question

I am trying to parse a JSON text using JSON_EXTRACT_PATH_TEXT() function. JSON sample:

{ 
   "data":[ 
      { 
         "name":"ping",
         "idx":0,
         "cnt":27,
         "min":16,
         "max":33,
         "avg":24.67,
         "dev":5.05
      },
      { 
         "name":"late",
         "idx":0,
         "cnt":27,
         "min":8,
         "max":17,
         "avg":12.59,
         "dev":2.63
      }
   ]
}
'

I tried JSON_EXTRACT_PATH_TEXT(event , '{"name":"late"}', 'avg') function to get 'avg' for name = "late", but it returns blank. Can anyone help, please? Thanks

GMB · Accepted Answer · 2021-02-23 10:58:34Z

This is a rather complicated task in Redshift, that, unlike Postgres, has poor support to manage JSON, and no function to unnest arrays.

Here is one way to do it using a number table; you need to populate the table with incrementing numbers starting at 0, like:

create table nums as
    select 0 i union all select 1 union all select 2 union all select 3 
    union all select 4 union all select 5 n union all select 6 
    union all select 7 union all select 8 union all select 9
;

Once the table is created, you can use it to walk the JSON array using json_extract_array_element_text(), and check its content with json_extract_path_text():

select json_extract_path_text(item, 'avg') as my_avg
from (
    select json_extract_array_element_text(t.items, n.i, true) as item
    from (
        select json_extract_path_text(mycol, 'data', true ) as items
        from mytable
    ) t
    inner join nums n on n.i < json_array_length(t.items, true)
) t
where json_extract_path_text(item, 'name') = 'late';

richyen · Accepted Answer · 2020-02-10 23:39:35Z

1

You'll need to use json_array_elements for that:

select obj->'avg'
  from foo f, json_array_elements(f.event->'data') obj 
where obj->>'name' = 'late';

Working example

create table foo (id int, event json);
insert into foo values (1,'{ 
   "data":[ 
      { 
         "name":"ping",
         "idx":0,
         "cnt":27,
         "min":16,
         "max":33,
         "avg":24.67,
         "dev":5.05
      },
      { 
         "name":"late",
         "idx":0,
         "cnt":27,
         "min":8,
         "max":17,
         "avg":12.59,
         "dev":2.63
      }]}');

answered Feb 10, 2020 at 23:39

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

Redshift Postgresql - How to Parse Nested JSON

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