问题:在PostgreSQL中查询嵌套的JSON数组

我目前正在试验 PostgreSQL 的 JSON 功能。虽然嵌套键值对对象的查询非常容易查询,但我在查询 JSON 数组时遇到了麻烦。更具体地说,我有一个表mytable有两列:一个整数主键id和一个 JSONB 列info。 JSONB 数据具有以下结构:

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

我想选择至少有一个name是x并且值介于15和25之间的params元素的所有表行。伪 SQL 查询将是:

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

我正在使用 PostgreSQL 9.6.2

解答

我假设您想获取存在具有提及值的数组元素的行的 id。 样本:

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

选择:

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)