时间戳

首页 > 解决方案 > 连接 json 字段 id 对应于其他表中的列的表

json - 连接 json 字段 id 对应于其他表中的列的表

问题描述

我有两个表，第一个表称为 orders，存储一个 json[] 数据类型，如下所示：

CREATE TABLE orders (id SERIAL, items: json[], price: REAL);

INSERT INTO orders (items, price) VALUES ('[{"itemId":1,"addons":["ice","cream"]},{"itemId":2,"addons":["mayo","peppers"]}]',12.4);

而另一个称为items，将与“itemId”对应的itemId存储在json数据类型中：

CREATE TABLE items(id SERIAL, name TEXT, price REAL);

INSERT INTO items(name,price) VALUES ('Chicken Bowl', 8.99);

我想以这样的方式加入表格，以从与订单表中的 itemId 对应的 items 表返回文本值，而不是 id 本身，例如为“itemId”返回“Chicken Bowl”而不是 1

标签： jsonpostgresql

解决方案

虽然模式对于这种类型的查询并不是很好，但这里有一种方法：

SELECT o.id, i.name
FROM orders AS o
    LEFT JOIN LATERAL unnest(o.items) AS u ON (true)
    INNER JOIN items AS i ON ((u->>'itemId')::int = i.id)
;

...产生（根据给定的测试数据）：

 id |     name     
----+--------------
  1 | Chicken Bowl
(1 row)

如果要将所有项目汇总到每个订单，请使用以下命令：

SELECT o.id AS order, array_agg(i.name) AS items
FROM orders AS o
    LEFT JOIN LATERAL unnest(o.items) AS u ON (true)
    INNER JOIN items AS i ON ((u->>'itemId')::int = i.id)
GROUP BY 1;

...生产：

 order |      items       
-------+------------------
     1 | {"Chicken Bowl"}
(1 row)

推荐阅读