How get value from jsonb in Postgres with queries on not ordered json arrays-CodePudding

I have in Postgres jsonb field with data like this. Every worker can have several phone numbers. Worker can have or not work's phone. Work's phone number can be first or last in array (not ordered).

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 }
            ]
        }
    ]
}
');

And I need to select and show table of workers who has work's number, like this:

Alex   | 916100203
Maria  | 915532355
Nina   | 915532321

Can I do it only with SQL jsonb queries in Postresql and how? Thank you.

CodePudding user response：

The following does this:

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"}]}'
;

The cross join jsonb_array_elements("json" -> 'workers') extracts all workers as rows from the JSON value. The where clause then limits that those rows that actually contain a work number.

This produces something like this:

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}]}

Now the SELECT list then extracts the worker's name name as one column and uses a JSON path query to find the first WORK number in the array of phones.

This will not work correctly if there is more than one array elements with a WORK phone for a worker.

Online example