Querying JSONB having array elements in Postgres

Question

I have a table named settings with the columns program_id(number), client_id(number), filters(Jsonb) and filters column has jsonb data in such format-

{
  "sources": [
    {
      "conditions": [
        {
          "value": [
            {
              "id": 1211,
              "name": "ABM BETA INVITE LIST",
              "isSelected": true
            }
          ],
          "condition": "one of the following"
        }
      ],
      "objectType": "SmartLists",
      "subscriptionId": 1173,
      "integrationType": "mkto"
    }
  ],
  "listType": "All Accounts",
  "programId": 30203,
  "noOfAccounts": null,
  "expiryDuration": 0,
  "subscriptionId": null,
  "updateFrequency": null
}

I now want to retrieve all the records from table settings where filters.sources[0].integrationType = 'mkto'. I have tried this query but gives me error of set-returning functions are not allowed in WHERE-

select * from settings where (jsonb_array_elements(filters -> 'sources') ->> 'integrationType' = 'mkto');

Erwin Brandstetter · Accepted Answer · 2022-12-15 12:43:43Z

2

I now want to retrieve all the records from table settings where filters.sources[0].integrationType = 'mkto'.

Using the #>> operator:

SELECT *
FROM   settings
WHERE  filters #>> '{sources, 0, integrationType}' = 'mkto';

fiddle

filters #>> '{sources, 0, integrationType}' is the same as:

filters -> 'sources' -> 0 ->> 'integrationType'
filters['sources'][0]['integrationType'] #>> '{}'  -- for Postgres 14+

But do you really only want to look at the first array element?

edited Dec 15, 2022 at 12:43

answered Dec 15, 2022 at 12:10

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

4 Comments

Srk95 Over a year ago

Yes @Erwin, I only want to look at the first array element always. Thanks a lot this query worked like a charm.

Srk95 Over a year ago

Hey @Erwin Brandstetter, could you also please help me with the update query for the same if I want to update the integrationType to pdto from mkto for such records.

Erwin Brandstetter Over a year ago

@Srk95: Go ahead and post a question if you can't find an answer on SO.

user330315 Over a year ago

Alternatively: with a JSON path expression: where filters @@ '$.sources[0].integrationType == "mkto"'

Collectives™ on Stack Overflow

Querying JSONB having array elements in Postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related