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

Querying jsonb column with nested objects and array of values in PostgreSQL?

I have a jsonb column with data like this:

{
    "rules": [
        {
            "type": "weekdays",
            "values": [
                3,
                5
            ]
        },
        {
            "type": "dates",
            "values": [
                "2024-01-01",
                "2024-01-02",
                "2024-01-03"
            ]
        }
    ]
}

What would a SELECT statement look like if I:

  1. Wanted to get all records WHERE ( rules[0].type.weekdays ).values CONTAINS 3:
SELECT * FROM table WHERE jsonb_column->>'rules'->0->'type'->'weekdays' : [the-result]->values = ANY ('{3}'::int[]);
  1. Wanted to get all records WHERE ( rules[0].type.dates ).values CONTAINS ‘2024-01-02’ (date-string):
SELECT * FROM table WHERE jsonb_column->>'rules'->0->'type'->'dates' ? [the-result]->values = ANY ('{"2024-01-03"}'::date[?]);

So, basically I need to find the object of a certain type AND THEN figure out whether that objects values contain what I’m looking for.

PSEUDO CODE:

SELECT * FROM table WHERE jsonb_column->rules[ how-to-iterate-array? ]->type = 'weekdays' AND [from-that-resulting-object]->>values CONTAIN 3;
Tags:

2

Answers


  1. 0

    I believe you’re looking for json paths which let you express such conditions. In your case, that would be

    WHERE jsonb_column @@ '$.rules[*] ? (@.type == "weekdays") .values[*] == 3'
WHERE jsonb_column @@ '$.rules[*] ? (@.type == "dates") .values[*] == "2024-01-03"'

    or

    WHERE jsonb_column @? '$.rules[*] ? (@.type == "weekdays" && @.values[*] == 3)'
WHERE jsonb_column @? '$.rules[*] ? (@.type == "dates" && @.values[*] == "2024-01-03")'
    Login or Signup to reply.
  2. 0

    you can use jsonpath and jsonb_path_exists for that:

    select *
from (values
    ('{ "rules": [
        {"type": "weekdays", "values": [3, 5]},
        {"type": "dates", "values": ["2024-01-01", "2024-01-02", "2024-01-03"]}
    ]}'::jsonb),
    ('{ "rules": [
        {"type": "weekdays", "values": [5, 4]},
        {"type": "dates", "values": ["2024-01-01", "2024-01-02", "2024-01-03"]}
    ]}'::jsonb)
) as a(data)
where
    jsonb_path_exists(a.data, '$.rules[*] ? (@.type == "weekdays").values[*] ? (@ == 3)')

    returns only one row:

    {"rules": [{"type": "weekdays", "values": [3, 5]}, {"type": "dates", "values": ["2024-01-01", "2024-01-02", "2024-01-03"]}]}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search