PostgreSQL JSONB – 具有变量键名的查询条件

发布时间：2020-12-13 18:04:45 所属栏目：百科来源：网络整理

导读：我已经阅读了各种 JSONB教程： 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

我已经阅读了各种 JSONB教程：

> 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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!