> https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/
> https://www.wagonhq.com/sql-tutorial/values-from-nested-json
> http://schinckel.net/2014/05/25/querying-json-in-postgres/
> http://stormatics.com/howto-use-json-functionality-in-postgresql/
请考虑以下示例.
有一个叫做计划的桌子.它包含以下列：
> id(整数,自动递增主键).
>名称(字符串).
>结构(jsonb).
结构列具有常规JSON对象,具有以下结构：
{
"some_unique_id": {
"key1": "valueA",// Fixed key name.
"key2": "valueB" // Fixed key name.
},"another_unique_id": {
"key1": "valueC",... // can go on up to a 1000 items.
}
注意：最外面的键是动态的.他们改变了每个项目.
这些值只是常规的JSON对象.没什么特别的.
我使用UUID作为结构中的键,因此如果我知道它的UUID,它很容易查找和检索特定值.
另一个选项是使我的结构成为一个对象数组(并将UUID作为值放在每个对象中),如下所示：
[
{
"uuid": "some_unique_id","key1": "valueA",{
"uuid": "another_unique_id","key1": "valueC",... // can go on up to a 1000 items.
]
在后一种方法中,要使用其UUID检索特定对象,我必须遍历整个数组并匹配每个对象的uuid键.
所以,我选择了第一种方法.
该表有3条记录.对于此问题,id和name列的值并不重要.
3条记录中结构列的实际值如下.
记录1：
{
"bab6246d-802c-4b80-af41-ab15fd1541b4": {
"name": "Sanskrit","children_uuids": [
"fa42b4b2-a958-42f1-af33-314e8e1fb6a6","3aeeadfe-6ad4-4229-85a5-5de030c08014"
],"is_invisible_node": true,"tags": [
"paper","course_paper"
],"type": "course_paper"
},"dbc33473-8453-4cf9-8ecf-d8013283b0d8": {
"name": "French","children_uuids": [
"4bf65ff9-3b11-42d5-a744-adcd1fd5a953"
],"type": "course_paper"
}
}
记录2：
{
"ed6164d0-fdc0-4259-90a5-fd60d9d716dc": {
"name": "Pen and Paper Assessment 1","children_uuids": [
],"is_invisible_node": false,"assessment"
],"type": "assessment"
},"059d0116-bca2-49f1-b333-58c4dbec8566": {
"name": "Content","type": "assessment"
}
}
记录3：
{
"63619c7f-fa73-49af-9df5-4be1eb38cee5": {
"name": "Q12","regular_paper"
],"type": "regular_paper"
},"56eed164-17f7-48e9-b3ce-b5b469e8cb0e": {
"name": "Q13","69d202c1-5c23-412f-860d-1a5d705c31b3": {
"name": "Q14","type": "regular_paper"
}
}
现在,如何编写查询来执行以下两项操作？
>我想获取包含is_invisible_node属性设置为true的任何对象的所有记录.
>我希望将包含regular_paper的所有对象作为其标记之一.
感谢您阅读这篇文章！任何帮助,将不胜感激.
I want to get all records which contain any objects with the is_invisible_node property set to true.
使用jsonb_each()在第二级检索对象：
select id,uuid.key uuid
from
plans,jsonb_each(structure) uuid
where (value->>'is_invisible_node')::boolean;
id | uuid
----+--------------------------------------
1 | bab6246d-802c-4b80-af41-ab15fd1541b4
1 | dbc33473-8453-4cf9-8ecf-d8013283b0d8
3 | 63619c7f-fa73-49af-9df5-4be1eb38cee5
(3 rows)
要么
select distinct id
from
plans,jsonb_each(structure) uuid
where (value->>'is_invisible_node')::boolean;
id
----
1
3
(2 rows)
I want to get all objects which contain regular_paper as one of its tags.
json对象标签是一个数组,所以使用jsonb_array_elements_text()是不需要的：
select uuid.key uuid
from
plans,jsonb_each(structure) uuid,jsonb_array_elements_text(value->'tags') tag
where tag = 'regular_paper';
uuid
--------------------------------------
56eed164-17f7-48e9-b3ce-b5b469e8cb0e
63619c7f-fa73-49af-9df5-4be1eb38cee5
69d202c1-5c23-412f-860d-1a5d705c31b3
(3 rows)
