在JSON列中查询数组的元素 [英] Query for element of array in JSON column
本文介绍了在JSON列中查询数组的元素的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
最近升级为使用PostgreSQL 9.3.1以利用JSON功能.在我的表格中,我有一个json类型列,其结构如下:
Recently upgraded to using PostgreSQL 9.3.1 to leverage the JSONfunctionalities. In my table I have a json type column that has a structure like this:
{
"id": "123",
"name": "foo",
"emails":[
{
"id": "123",
"address": "somethinghere"
},
{
"id": "456",
"address": "soemthing"
}
]
}
这只是虚假数据.
是否可以根据ID在emails数组中查询特定项目?
差不多:在id = 123的地方返回电子邮件"?
Is it possible to query for a specific item in the emails array based on the id?
Pretty much: "return email where id=123)"?
推荐答案
是的,有可能:
SELECT *
FROM tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE elem->>'id' = 123;
tbl
是您的表名,json_col
是JSON列的名称.
tbl
being your table name, json_col
being the name of the JSON column.
此相关答案中的更多详细信息:
More details in this related answer:
此相关答案的最后一段中有关隐式CROSS JOIN LATERAL
的更多信息:
More about the implicit CROSS JOIN LATERAL
in the last paragraph of this related answer:
支持这种查询的索引:
这篇关于在JSON列中查询数组的元素的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文