Querying nested JSON arrays in PostgreSQL

Question

I am currently experimenting with the JSON functionality PostgreSQL. While queries of nested key-value pair objects are very easy to query, I am having trouble querying JSON arrays. To be more specific, I have a table mytable with two columns: An integer primary key id and a JSONB column info. The JSONB data have the following structure:

{
  "modes": [
    {
      "params": [
        {"name": "x", "value": 10},
        {"name": "y", "value": 15}
      ]
    },
    {
      "params": [
        {"name": "x", "value": 20},
        {"name": "y", "value": 25}
      ]
    }
  ]
}

I want to select all table rows that have at least one params element whose name is x and has a value between 15 and 25. A pseudo-SQL query would be:

SELECT 
  * 
FROM 
  mytable 
WHERE 
  (info->'modes[*]'->'params[*]'->>'name')::TEXT = 'x'
  AND
  (info->'modes[*]'->'params[*]'->>'value')::FLOAT BETWEEN 15 AND 25;

I am using PostgreSQL 9.6.2

Possible duplicate of Query elements in a nested array of a json object in postgresql 9.4 or 9.5 — McNets
– McNets, Commented Mar 30, 2017 at 10:19

Vao Tsun · Accepted Answer · 2017-03-30 12:03:52Z

7

I assumed you want to get id of row where exists array element with mentionned values. sample:

t=# create table so40 (i int, j json);
CREATE TABLE
t=# insert into so40 select 1,'{
t'#   "modes": [
t'#     {
t'#       "params": [
t'#         {"name": "x", "value": 10},
t'#         {"name": "y", "value": 15}
t'#       ]
t'#     },
t'#     {
t'#       "params": [
t'#         {"name": "x", "value": 20},
t'#         {"name": "y", "value": 25}
t'#       ]
t'#     }
t'#   ]
t'# }';
INSERT 0 1

select:

with f as (
        with t as (
                with j as (select * from so40)
                select *,json_array_elements(j->'modes')->'params' p from j
        )
        select *,json_array_elements(p)->>'name' n,json_array_elements(p)->>'value' v from t
)
select i,j,n,v
from f
where  n  ='x' and v::int between 15 and 25
;
 i |                  j                  | n | v
---+-------------------------------------+---+----
 1 | {                                  +| x | 20
   |   "modes": [                       +|   |
   |     {                              +|   |
   |       "params": [                  +|   |
   |         {"name": "x", "value": 10},+|   |
   |         {"name": "y", "value": 15} +|   |
   |       ]                            +|   |
   |     },                             +|   |
   |     {                              +|   |
   |       "params": [                  +|   |
   |         {"name": "x", "value": 20},+|   |
   |         {"name": "y", "value": 25} +|   |
   |       ]                            +|   |
   |     }                              +|   |
   |   ]                                +|   |
   | }                                   |   |
(1 row)

edited Mar 30, 2017 at 12:03

answered Mar 30, 2017 at 10:37

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

5 Comments

AstrOne Over a year ago

Thank you for the quick answer my friend! Actually I want to get the entire row, not only the id. I have been trying to modify your code to achieve that but I haven't had much success so far. Any additional help is more than welcome!

AstrOne Over a year ago

I modified the last line to this: SELECT * FROM so40, f WHERE so40.i = f.i AND n = 'x' AND v::FLOAT BETWEEN 15 AND 25; And everything seems to work. But is it correct? Is there a better way to do it? Could oyu edit your post with correct modification so I can accept it please?

AstrOne Over a year ago

Also it would be nice if the formatting didn't have these t'# all over the place, so it would be easy for other users to read. Thank you so much.

Vao Tsun Over a year ago

you don't need to join the initial table again. I changed answer so it would not skip wanted columns @AstrOne

AstrOne Over a year ago

Looks like it is working just fine! Thanks a lot my friend!

Collectives™ on Stack Overflow

Querying nested JSON arrays in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related