时间戳

首页 > 解决方案 > PostgreSQL 将 JSON 对象作为键值对返回

json - PostgreSQL 将 JSON 对象作为键值对返回

问题描述

PostgreSQL 实例以 JSONB 格式存储数据：

CREATE TABLE myschema.mytable (
    id   BIGSERIAL PRIMARY KEY,
    data JSONB     NOT NULL
)

数据数组可能包含如下对象：

{
  "observations": {
    "temperature": {
      "type": "float",
      "unit": "C",
      "value": "23.1"
    },
    "pressure": {
      "type": "float",
      "unit": "mbar",
      "value": "1011.3"
    }
  }
}

选定的行应以键值对的形式返回，格式如下：

temperature,type,float,value,23.1,unit,C,pressure,type,float,value,1011.3,unit,mbar

以下查询至少返回每个对象，同时仍返回 JSON：

SELECT id, value FROM mytable JOIN jsonb_each_text(mytable.data->'observations') ON true;
1 | {"type": "float", "unit": "mbar", "value": 1140.5}
1 | {"type": "float", "unit": "C", "value": -0.9}
5 | {"type": "float", "unit": "mbar", "value": "1011.3"}
5 | {"type": "float", "unit": "C", "value": "23.1"}

但结果是拆分的，而不是文本格式。

如何返回中所有对象的键值对data？

标签： jsonpostgresqljsonb

解决方案

这将扁平化 json 结构并有效地将值与顶级键名（例如温度和压力）连接起来，以获得预期的“深度”级别。看看这是不是你的想法。

SELECT
    id,
    (
      SELECT STRING_AGG(conc, ',')
      FROM (
        SELECT CONCAT_WS(',', key, STRING_AGG(value, ',')) AS conc
        FROM (
          SELECT key, (jsonb_each_text(value)).value
          FROM jsonb_each(data->'observations')
        ) AS x
        GROUP BY key
      ) AS csv
    ) AS csv
FROM mytable

结果：

| id  | csv                                                 |
| --- | --------------------------------------------------- |
| 1   | pressure,float,mbar,1011.3,temperature,float,C,23.1 |
| 2   | pressure,bigint,unk,455,temperature,int,F,45        |

https://www.db-fiddle.com/f/ada5mtMgYn5acshi3WLR7S/0

推荐阅读