Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE products(
   id text,
   attributes jsonb,
   PRIMARY KEY(id)
);

CREATE TABLE


INSERT INTO products VALUES ('id1', '{
      "endDate": [
        "2024-02-20T21:00:00.000Z"
      ],
      "countries": [
        "US","IT","ES"
      ],
      "type": [
        "RETAIL"
      ],
      "startDate": [
        "2024-02-13T08:00:00.000Z"
      ],
      "categories": [
        "ELECTRONICS"
      ],
      "currency": [
        "USD"
      ],
      "status": [
        "ACTIVE"
      ]
    }');

INSERT 0 1


SELECT *
FROM products
WHERE
  attributes -> 'countries' ?| array['US', 'MX', 'JP']


id
attributes


id1
{"type": ["RETAIL"], "status": ["ACTIVE"], "endDate": ["2024-02-20T21:00:00.000Z"], "currency": ["USD"], "countries": ["US", "IT", "ES"], "startDate": ["2024-02-13T08:00:00.000Z"], "categories": ["ELECTRONICS"]}



SELECT 1


SELECT *
FROM products
WHERE
  attributes @? '$.countries[*] ? (@ == "US" || @ == "MX" || @ == "JP")'


id
attributes


id1
{"type": ["RETAIL"], "status": ["ACTIVE"], "endDate": ["2024-02-20T21:00:00.000Z"], "currency": ["USD"], "countries": ["US", "IT", "ES"], "startDate": ["2024-02-13T08:00:00.000Z"], "categories": ["ELECTRONICS"]}



SELECT 1


SELECT *
FROM products
WHERE EXISTS (
  SELECT value
  FROM jsonb_array_elements_text(attributes -> 'countries')
  INTERSECT
  SELECT *
  FROM UNNEST(array['US', 'MX', 'JP'])
)
  


id
attributes


id1
{"type": ["RETAIL"], "status": ["ACTIVE"], "endDate": ["2024-02-20T21:00:00.000Z"], "currency": ["USD"], "countries": ["US", "IT", "ES"], "startDate": ["2024-02-13T08:00:00.000Z"], "categories": ["ELECTRONICS"]}



SELECT 1