> 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 uuIDfrom plans,Jsonb_each(structure) uuIDwhere (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 IDfrom plans,Jsonb_each(structure) uuIDwhere (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 uuIDfrom plans,Jsonb_each(structure) uuID,Jsonb_array_elements_text(value->'Tags') tagwhere tag = 'regular_paper'; uuID -------------------------------------- 56eed164-17f7-48e9-b3ce-b5b469e8cb0e 63619c7f-fa73-49af-9df5-4be1eb38cee5 69d202c1-5c23-412f-860d-1a5d705c31b3(3 rows)总结
以上是内存溢出为你收集整理的PostgreSQL JSONB – 具有变量键名的查询条件全部内容,希望文章能够帮你解决PostgreSQL JSONB – 具有变量键名的查询条件所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)