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

Vao Tsun Vao Tsun · Accepted Answer · 2017-03-30T10:37:58

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)

Querying nested JSON arrays in PostgreSQL

1 Answers