Postgres 扩展对象数组 (jsonb)

Question

我有一个 postgres 表，我想在其中扩展 jsonb 列。

该表（称为“runs”）将每个参与者视为一行，其中 jsonb 列保存他们的实验数据。

id |data         |
---|-------------|
id1|[{}, {}]     |
id2|[{}, {}, {}] |

jsonb 列始终是一个包含未知数量对象的数组，其中每个对象都有未知数量的任意键。

[
  {
    "rt": 3698,
    "phase": "questionnaire",
    "question": 1
  },
  {
    "rt": 3698,
    "phase": "forced-choice",
    "choice": 0,
    "options": ["red", "blue"]
  }
]

我想 (1) 展开 jsonb 列，以便每个对象都是一行：

id |rt    | phase         | question | choice | options        |
---|------| ------------- | -------- | ------ | -------------- |
id1| 3698 | questionnaire | 1        |        |                |
id1| 5467 | choice        |          | 0      | ["red", "blue] |

或 (2) 将行中的其他列映射到 jsonb 数组（此处为“id”键）：

[
  { 
    "id": "id1",
    "rt": 3698,
    "phase": "questionnaire",
    "question": 1
  },
  {
    "id": "id1",
    "rt": 3698,
    "phase": "forced-choice",
    "choice": 0,
    "options": ["red", "blue"]
  }
]

事实上，对象的数量，每个对象的键数量，以及键本身是先验未知的，这确实让我很难完成这个任务。也许是这样的，但这是不对的......

SELECT id, x.*
FROM
 runs_table, 
 jsonb_populate_recordset(null:runs_table, data) x

Answer 1

PostgreSQL 有许多 JSON 函数。首先，您必须从“JSONB”中提取键和值。之后，您可以使用 Postgres jsonb_typeof(jsonb) 函数获取值的类型。我为您编写了两个示例：

-- sample 1 
select * 
from jsonb_array_elements(
    '[
      { 
        "id": "id1",
        "rt": 3698,
        "phase": "questionnaire",
        "question": 1
      },
      {
        "id": "id1",
        "rt": 3698,
        "phase": "forced-choice",
        "choice": 0,
        "options": ["red", "blue"]
      }
    ]'::jsonb 
) t1 (json_data)
cross join jsonb_each(t1.json_data) t2(js_key, js_value)
where jsonb_typeof(t2.js_value::jsonb) = 'array' 

-- sample 2
select *
from jsonb_array_elements(
    '[
      { 
        "id": "id1",
        "rt": 3698,
        "phase": "questionnaire",
        "question": 1
      },
      {
        "id": "id1",
        "rt": 3698,
        "phase": "forced-choice",
        "choice": 0,
        "options": ["red", "blue"]
      }
    ]'::jsonb 
) t1 (json_data)
where jsonb_typeof((t1.json_data->'options')::jsonb) = 'array' 

示例 1： 此查询将从 JSONB 中提取所有键和值，然后设置过滤以仅显示数组类型的值。

示例 2：如果您知道哪些键可以是数组类型，请使用此查询。