Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create temp table extract_jsonb(data jsonb);
insert into extract_jsonb(data) values (
'{
  "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
      }
    ]
  }
}');

CREATE TABLE



INSERT 0 1


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 ;
 
 


verifystateid
id
lastname
fullnumber


1
"adf59079-4921-4abc-a262-1dc8c2b1ccc7"
"LOBATOS"
"0671234567"



SELECT 1