如何在未订购的JSON数组的Postgres中从JSONB获得价值

Question

我在Postgres JSONB字段中使用了这样的数据。每个工人都可以有几个电话号码。工人可以或不工作的电话。工作的电话号码可以是阵列中的第一个或最后一个（未订购）。

INSERT INTO "contacts" ("id", "json") VALUES
(101, ' 
{
    "add-date": "2022-06-04",
    "workers": [
        {
            "name": "Alex",
            "phones": [
                { "type": "HOME", "number": 926117171 },                
                { "type": "WORK", "number": 916100203 },                
                { "type": "CELL", "number": 911463212 }             
            ]
        },
        {
            "name": "Maria",
            "phones": [
                { "type": "HOME", "number": 919351948 },
                { "type": "WORK", "number": 915532355 }
            ]
        }
    ]
}
'),
(102, '
{
    "add-date": "2022-06-05",
    "workers": [
        {
            "name": "Pablo",
            "phones": [
                { "type": "CELL", "number": 913456719 }
            ]
        },
        {
            "name": "Nina",
            "phones": [
                { "type": "WORK", "number": 915532321 },
                { "type": "CELL", "number": 919455354 }
            ]
        }
    ]
}
');

我需要选择和显示具有工作号码的工人表，这样的工人：

Alex   | 916100203
Maria  | 915532355
Nina   | 915532321

我只能使用postresql中的SQL+JSONB查询来做到这一点？

Answer 1

以下执行此操作：

select c.id, w.value ->> 'name' as name,
       jsonb_path_query_first(w.value, '$.phones[*] ? (@.type == "WORK")') ->> 'number' as work_number
from contacts c
  cross join jsonb_array_elements("json" -> 'workers') as w(value)
where w.value @> '{"phones": [{"type": "WORK"}]}'
;

交叉加入JSONB_ARRAY_ELEMENTS（“ JSON” - ＆gt;'工人'）将所有工人从JSON值中提取为行。然后，其中的子句限制了那些实际包含一个工作号的行。

这会产生类似的内容：

id  | value                                                                                                                                            
----+--------------------------------------------------------------------------------------------------------------------------------------------------
101 | {"name": "Alex", "phones": [{"type": "HOME", "number": 926117171}, {"type": "WORK", "number": 916100203}, {"type": "CELL", "number": 911463212}]}
101 | {"name": "Maria", "phones": [{"type": "HOME", "number": 919351948}, {"type": "WORK", "number": 915532355}]}                                      
102 | {"name": "Nina", "phones": [{"type": "WORK", "number": 915532321}, {"type": "CELL", "number": 919455354}]}                                       

现在选择列表然后将工人的名称提取为一列，并使用JSON路径查询来查找手机数组中的第一个工作号。

如果有一个以上的阵列元素和工人的工作电话，这将无法正常工作。