Question 114550 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Returning a list containing certain parameters from an array of json in PSQL

I am having some trouble figuring out on extracting a parameter (contactId) from a JSON list, and returning the extracted parameters in a list.

Data

Table Name: users

       contact
      -----------------------
1     {
       "mainContact": {
         name: "Timmy Tom",
         contactId: "11111"
         phoneNumber: "+12345"
      },
       "contactList": [
         {
          name: "Timmy Tom",
          contactId: "11111",
          phoneNumber: "+12345"
        },
        {
          name: "Robin Tom",
          contactId: "11112",
          phoneNumber: "+12345"
        },
        {
          name: "Jerry Tom",
          contactId: "11113",
          phoneNumber: "+12345"
        }
      ]
     }

2     {
       "mainContact": {
         name: "Timmy Bob",
         contactId: "21111"
         phoneNumber: "+12345"
      },
       "contactList": [
         {
          name: "Timmy Bob",
          contactId: "21111",
          phoneNumber: "+12345"
        },
        {
          name: "Robin Bob",
          contactId: "21112",
          phoneNumber: "+12345"
        },
        {
          name: "Jerry Bob",
          contactId: "21113",
          phoneNumber: "+12345"
        }
      ]
     }

I am able to filter the contactList out using the query:

SELECT contact -> 'contactList' as contact_list from users;

However, this is not what I need. I need to return the following response which only contains the contactId from the contactList in a JSON array but am unsure how to proceed further:

    contact_list
    ------------
1  [11111, 11112, 11113]
2. [21111, 21112, 21113]

Failed Attempts:

1. with query as (SELECT contact -> 'contactList' as contact_list from users)
select (contact_list ->> 'contactId') from query;
--> This query returns nothing, which I find puzzling

Would appreciate some guidance on this.. Thank you

Tags:

2

Answers


  1. 0

    You can use a JSON path query:

    select jsonb_path_query_array(contact, '$.contactList[*].contactId')
from users;
    Login or Signup to reply.
  2. 0

    First of all we use jsonb_array_elements function to unset jsonb array to rows and after that we aggregate them using string_agg

    with contactIds as (
    select 
        id, jsonb_array_elements(contact->'contactList')->>'contactId' as contact_list 
    from users
) select id, string_agg(contact_list, ',') from contactIds group by id;

    online sql editor

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search