Complex grouping of Postgres jsonb column

Question

I have table with a jsonb field and Postgres 12.1

create table market (
    user int primary key,
    base jsonb
);

the jsonb field has following structure

{
    "a": [1, 2, 3],
    "regions": [
        {
            "id": 1,
            "name": "name",
            "description": "description",
            "shops": [
                {
                    "id": 11,
                    "brands": [
                        {
                            "name": 22,
                            "id": 21
                        }
                    ]
                }
            ]
        }
    ]
}

Our cliens choose a shop and then the brands. This chosing are stored in the market table. A user does his chose {shopId: 1, brands[1, 2, 3]}. I search how often the user chose this shop and this brands. In result i expected region_id, region_name, shop_id, count_of_using_shop_id, brand_id, count_of_using_brand_id

I have legacy the market table with several millions rows. I do not work with jsonb earlier and i am confused about that deep nested structure.

I think do this with using group by, but after several experiments i rejected this solution. Results are a very large number and group by operator performs costly sort.

Can you help me and point basic direction to solve my problem? Can it be more fastly directly with python with no sql? Of course i will be select all data to analyze with plain sql and then will be filtering shops and brands with python?

Laurenz Albe · Accepted Answer · 2021-09-03 11:11:12Z

0

That's a terrible, terrible data model.

To query that efficiently, you need a GIN index on the column:

CREATE INDEX ON market USING gin (base);

Then, to find all rows that match {shopId: 1, brands[1, 2, 3]}, you'd have to use the containment operator @> and split the OR that in implied by the array into UNION ALL queries:

SELECT * FROM market
WHERE data @> '{ "regions": [ { "shops": [ { "id": 1, "brands": [ { "id": 1 } ] } ] } ] }'
UNION ALL
SELECT * FROM market
WHERE data @> '{ "regions": [ { "shops": [ { "id": 1, "brands": [ { "id": 2 } ] } ] } ] }'
UNION ALL
SELECT * FROM market
WHERE data @> '{ "regions": [ { "shops": [ { "id": 1, "brands": [ { "id": 3 } ] } ] } ] }';

answered Sep 3, 2021 at 11:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

K65ty4dfd0 Over a year ago

Thank you for your faster feedback. Can you offer how can i reorginize my jsonb field structure to boost performance

Laurenz Albe Over a year ago

You wouldn't use JSON at all. You'd have several tables like region, shop and brand, and each array element would become one table row. The relationships between the tables are foreign key constraints.

K65ty4dfd0 Over a year ago

Thank you very much. Your answer was useful for me

Collectives™ on Stack Overflow

Complex grouping of Postgres jsonb column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related