Query a column with jsonb data meeting certain criteria

Problem

I have a table like this:

product	tags (jsonb)
P001	[{“name”: “LX”,”active”: true}, {“name”: “TX”,”active”: true}]
P002	[{“name”: “LX”,”active”: true}]

I am trying to query against this table to get a list of products with tags that are the same.

I found the following to be insufficient for my query since it will match all products with at least the tag I query for:

SELECT product
FROM product_table 
WHERE tags @> '[
    {"name": "LX","active": true}
]';

Result

product
P001
P002

So I need the match to be more exact without being so strict as to demand order of tag objects in the array. For example:

[
    {
        "name": "LX",
        "active": true
    },
    {
        "name": "TX",
        "active": true
    }
]
-- Interpreted the same as
[
    {
        "name": "TX",
        "active": true
    },
    {
        "name": "LX",
        "active": true
    }
]

Desired Result

A list of products that match only the tags in the query.

Resources

A list of the resources I am using to try and solve the problem.

• 9.16. JSON Functions and Operators
• How To Query a JSONB Array of Objects as a Recordset in PostgreSQL
• Working with a JSONB Array of Objects in PostgreSQL

Answer

Base on the example you give, you can use simultaneous containment in both directions.

...WHERE tags <@ :whatever and tags @> :whatever

If you don’t like repeating the arguments, you could make a custom function, or operator:

create function equal_but_for_order(jsonb,jsonb) returns boolean language sql as $$ 
    select $1 <@ $2 and $1 @> $2
$$;

create operator <@> (function = equal_but_for_order, leftarg = jsonb, rightarg=jsonb);

...WHERE tags <@> :whatever