Postgres JSONB:数组数组的where子句 [英] Postgres JSONB: where clause for arrays of arrays

问题描述

在postgres中(如果有问题，请参见9.5版):

there is in postgres (v 9.5, if it is matter):

create table json_test(
 id varchar NOT NULL,
 data jsonb NOT NULL,
 PRIMARY KEY(id)
);

数据为json且包含数组数组

Where data is json and contains array of arrays

{
    "attribute": "0",
    "array1": [{
        "id": "a12",
        "attribute": "1",
        "array2": [{
            "id": "a21",
            "attribute": "21"
        }]
    },
    {
        "id": "a12",
        "attribute": "2",
        "array2": [{
            "id": "22",
            "attribute": "22"
        }]
    }]
}

必填:

select id from json_test where 
    json_test->>'attribute'='0' and
    array1.[id='a12'].array2.attribute='22'

查询的意思是:给我所有ID

Query should mean: give me all ids where

1. 某些顶级属性具有特定的值
2. 数组中的特定对象具有必需的属性
3. 特定于array1的某些对象(来自array2)具有必需的属性

诀窍是如何实现最后一个条件.

the trick is how to implement the last condition.

另一个例子:

{
    "attribute": "0",
    "array1": [{
        "id": "a12",
        "attribute": "1",
        "array2": [{
            "id": "a21_1",
            "attribute_1": "21_1"
        },{
            "id": "a21_2",
            "attribute_2": "21_2"
        }]
    }]
}

select * from json_test where 
    array1.[id='a12'].array2.attribute_1='21_1' and  
    array1.[id='a12'].array2.attribute_2='21_2'

推荐答案

检索嵌套json数组的最通用方法是使用多个

The most general way to retrieve nested json arrays is to use multiple jsonb_array_elements() in lateral join. Example:

with json_test(id, data) as (
    values
        (1, 
        '{
            "attribute": "0",
            "array1": [{
                "id": "a12",
                "attribute": "1",
                "array2": [{
                    "id": "a21",
                    "attribute": "21"
                }]
            },
            {
                "id": "a12",
                "attribute": "2",
                "array2": [{
                    "id": "22",
                    "attribute": "22"
                }]
            }]
        }'::jsonb)
    )

select id, elem2
from 
    json_test, 
    jsonb_array_elements(data->'array1') array1(elem1),
    jsonb_array_elements(elem1->'array2') array2(elem2)
where elem2->>'id' = '22';

 id |              elem2              
----+---------------------------------
  1 | {"id": "22", "attribute": "22"}
(1 row)

该方法是通用的，因为您可以轻松访问任何级别的任何json对象的任何值，例如:

The method is general because you can easily access any value of any json object on any level, e.g.:

...
where 
    data->>'attribute' = '0'
    and elem1->>'id' = 'a12'
    and elem2->>'id' = 'a21_1';

这篇关于Postgres JSONB:数组数组的where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！