Postgres JSON array - how to do select fields

Question

I have table in Postgres with JSONB filed. JSONB contains jsons as:

{
  "id": "adf59079-4921-4abc-a262-1dc8c2b1ccc7",
  "lastname": "LOBATOS",
  "firstname": "Leslie",
  "birth_date": "1988-01-26",
  "gender": 3,
  "contacts": {
    "phoneList": [
      {
        "fullNumber": "0671234567",
        "verifyStateId": 1
      },
      {
        "fullNumber": "0671234588",
        "verifyStateId": 0
      }
    ]
  }
}

I need select following data-set (in SQL notation)

SELECT id, lastname, fullNumber FROM <JSONB-field> 
WHERE fullNumber LIKE '067%' and verifyStateId = 1

Plz help write query

sorry. version is PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit — Burning Daylight
– Burning Daylight, Commented May 31, 2022 at 9:20

user330315 · Accepted Answer · 2022-05-31 09:58:06Z

2

You can use a JSON path expression to filter out the needed rows:

where the_column @? '$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)'

To actually get the fullNumber you need to repeat the JSON path in order to extract the array element in question:

select id, 
       the_column ->> 'lastname', 
       jsonb_path_query_first(the_column, 
                              '$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)'
                             ) ->> 'fullNumber' as fullnumber
from the_table
where the_column @? '$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)'

The WHERE condition can potentially make use of a GIN index on the_column to improve performance.

If there is no such index or performance isn't that important, you can avoid repeating the JSON path by using a derived table:

select *
from (
  select id, 
         the_column ->> 'lastname', 
         jsonb_path_query_first(the_column, '$.contacts.phoneList[*] ? (@.fullNumber like_regex "^067" && @.verifyStateId == 1)') ->> 'fullNumber' as fullnumber
  from the_table
) t 
where fullnumber is not null

answered May 31, 2022 at 9:58

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Burning Daylight Over a year ago

THANKS! Best performance = 84ms!!!

Burning Daylight Over a year ago

could you hint smth about this subject: stackoverflow.com/questions/72458472/…

Slava Rozhnev · Accepted Answer · 2022-05-31 09:58:33Z

1

You can use next query:

with unnested as (
  select 
    fld->>'id' id, fld->>'lastname' lastname, 
    jsonb_array_elements(((fld->>'contacts')::jsonb->>'phoneList')::jsonb)
from tbl
) select id, lastname, jsonb_array_elements->>'fullNumber' from unnested;

PostgreSQL fiddle

+======================================+==========+============+
| id                                   | lastname | ?column?   |
+======================================+==========+============+
| adf59079-4921-4abc-a262-1dc8c2b1ccc7 | LOBATOS  | 0671234567 |
+--------------------------------------+----------+------------+
| adf59079-4921-4abc-a262-1dc8c2b1ccc7 | LOBATOS  | 0671234588 |
+--------------------------------------+----------+------------+

answered May 31, 2022 at 9:58

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

1 Comment

Burning Daylight Over a year ago

thanks a lot. but performance is 6.24s.

jian · Accepted Answer · 2022-05-31 09:58:33Z

1

demo

WITH cte AS (
    SELECT
        jsonb_path_query(data, '$.contacts.phoneList[*].verifyStateId')::text AS verifyStateId,
        jsonb_path_query(data, '$.id')::text AS id,
        jsonb_path_query(data, '$.lastname')::text AS lastname,
        jsonb_path_query(data, '$.contacts.phoneList[*].fullNumber')::text AS fullnumber
    FROM
        extract_jsonb
)
SELECT
    *
FROM
    cte
WHERE
    verifyStateId = '1'::text
    AND fullnumber ILIKE '"067%'::text;

since cast to text, somehow the first character of fullnumber is "

answered May 31, 2022 at 9:58

jian

5,0131 gold badge29 silver badges46 bronze badges

1 Comment

Burning Daylight Over a year ago

thanks a lot. but performance is more 11s.

Collectives™ on Stack Overflow

Postgres JSON array - how to do select fields

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related