PostgreSQL 仅选择 jsonb 数据中的值 [英] PostgreSQL SELECT only values inside jsonb data

问题描述

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

SELECT data FROM orders;

<预><代码> [{food_id":1"，表":A12"，},{food_id":2"，表":A14"，}]

我可以通过按原样提供数据来轻松选择，但如何将其转换为简化?

我的预期结果:

<代码>选择???作为 food_tables FROM 订单；

[A12"，A14"]

我个人还是不明白 jsonb_array_elements() 是如何工作的.

谢谢！

解决方案

您可以对未嵌套的数组元素执行横向交叉连接并提取属性:

SELECT jsonb_agg(d.elem -> 'table')FROM 订单横向交叉连接 jsonb_array_elements(orders.data) AS d(elem)GROUP BY orders.id;

如果需要 PostgreSQL 数组，请使用 array_agg 而不是 jsonb_agg.

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

I have a postgresql data with values (it is jsonb type column):

SELECT data FROM orders;

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

I can easily SELECT by providing data as it is, but how to convert it into simplified ?

My expected result:

SELECT ??? as food_tables FROM orders;

["A12", "A14"]

I personally still did not understand how jsonb_array_elements() works.

Thanks!

解决方案

You could perform a lateral cross join with the unnested array elements and extract the attributes:

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

Use array_agg instead of jsonb_agg if you want a PostgreSQL array.

It is a mistake to model tabular data as a JSON array. Change your data model so that each array element becomes a row in a database table.

这篇关于PostgreSQL 仅选择 jsonb 数据中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！