Query from list of lists from JSON field

Question

I have a table in postgresql, which contains JSON list, each element of is also a list (it could be dictionary though I decided to make it list or tuple).

Here is an example:

+---------------------------------------------------+
| [["status", 10], ["status", 20]]                  |
+---------------------------------------------------+
| [["extra", 21], ["status", 15]]                   |
+---------------------------------------------------+
| [["value", 33]]                                   |
+---------------------------------------------------+
| [["extra", 21], ["status", 15], ["feature", 11]]  |
+---------------------------------------------------+

I would like to find maximum status, i.g. zero-th element should be equal to "status" and get maximum value of the first element. So far I could not event extract the data, here is what I've tried:

SELECT value->0 FROM jsonb_array_elements((SELECT items::jsonb FROM mytable LIMIT 1));

With this query I can expand first row (LIMIT 1), remove limitation gives me an error:

ERROR:  more than one row returned by a subquery used as an expression

I believe you can emulate my situation with next query:

select * from json_array_elements((select * from json_array_elements(
    '[ [["status", 10], ["status", 20]], [["extra", 21], ["status", 15]], [["value", 33]], [["extra", 21], ["status", 15], ["feature", 11]] ]'
)));

Could someone help me to expand/flatten values?

klin · Accepted Answer · 2017-05-19 12:31:01Z

3

with my_table(items) as (
values
    ('[["status", 10], ["status", 20]]'::jsonb),
    ('[["extra", 21], ["status", 15]]'),
    ('[["value", 33]]'),
    ('[["extra", 21], ["status", 15], ["feature", 11]]')
)

select max((value->>1)::int)
from (
    select value
    from my_table,
    jsonb_array_elements(items)
    where value->>0 = 'status'
    ) s;

 max 
-----
  20
(1 row)

However, the json data format is a bit strange. More natural approach would be e.g. [{"status": 10}, {"status": 20}].

answered May 19, 2017 at 12:31

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

grundic Over a year ago

thank you -- worked like a charm! I agree that format slightly strange. Our dba said that using tuples instead of dicts could save a little bit space. The number of fields is more then 2 and we expect up to 100 items in each record. My first approach was with another table, but later we decided to go with jsonb.

Collectives™ on Stack Overflow

Query from list of lists from JSON field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related