PostgreSQL SELECT仅在jsonb数据中使用值

Question

我有一个带有值的postgresql数据(它是jsonb类型的列(：

SELECT data FROM orders;

[
{
"food_id": "1",
"table": "A12",
},
{
"food_id": "2",
"table": "A14",
}
]

我可以通过按原样提供数据来轻松地进行SELECT，但如何将其转换为简化的数据？

我的预期结果：

SELECT ??? as food_tables FROM orders;

["A12", "A14"]

我个人仍然不理解jsonb_array_elements()是如何工作的。

谢谢！

Answer 1

如果您使用的是Postgres 12或更高版本，则可以使用jsonb_path_query_array()

select jsonb_path_query_array(data, '$[*].table') as food_tables
from orders

Answer 2

您可以对未测试的数组元素执行横向交叉连接，并提取属性：

SELECT jsonb_agg(d.elem -> 'table')
FROM orders
CROSS JOIN LATERAL jsonb_array_elements(orders.data) AS d(elem)
GROUP BY orders.id;

如果您想要PostgreSQL数组，请使用array_agg而不是jsonb_agg。

将表格数据建模为JSON数组是错误的。更改数据模型，使每个数组元素成为数据库表中的一行。