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

CodePudding user response：

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?