查询JSON列中数组的元素

Question

最近升级到使用PostgreSQL 9.3.1来利用JSON功能。在我的表中，我有一个json类型列，其结构如下：

{
   "id": "123",
   "name": "foo",
   "emails":[
      {
        "id": "123",
        "address": "somethinghere"
      },
      {
        "id": "456",
        "address": "soemthing"
      }
   ]
} 

就问题而言，这只是虚拟数据。

是否可以根据id查询电子邮件数组中的特定项目
差不多：“返回id=123的电子邮件）”？

Answer 1

看到这篇文章，发现您可以像这样直接查询表：

SELECT *
FROM   table_name, json_array_elements(json_column) AS data
WHERE  data->>'id' = 123;

省略此部分：

json_array_elements(t.json_col->'emails')

Answer 2

使用Postgres 9.4中的JSONB列，您可以使用包含运算符@

SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

有关更多详细信息，请参阅查询JSON类型内的数组元素。

以下是一个工作示例：

CREATE TABLE jsontest(data JSONB NOT NULL);
INSERT INTO jsontest VALUES (
  '{
     "name": "foo",
     "id": "123",
     "emails": 
     [
       {
         "address": "somethinghere",
         "id": "123"
       },
       {
         "address": "soemthing",
         "id": "456"
       }
     ]
  }'
);
SELECT * FROM jsontest WHERE data @> '{ "emails": [{ "id": "123" }] }';

data
----
{"id": "123", "name": "foo", "emails": [{"id": "123", "address": "somethinghere"}, {"id": "456", "address": "soemthing"}]}

（1排）

Answer 3

关于Postgres 9.4，请参见adamc稍后的回答。或：

• 查询JSON类型内的数组元素

是的，这是可能的：

SELECT *
FROM   tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE  elem->>'id' = 123;

tbl是您的表名，json\u col是json列的名称。

另请参见：

• 如何使用新PostgreSQL JSON数据类型中的字段进行查询

关于隐式横向交叉连接：

• 带有元素号的PostgreSQL unnest（）

支持此类查询的索引：

• 在JSON数组中查找元素的索引