1

Question

How can you make a PostgreSQL View based on a JSONB field object array, with appropriate indexes? Example below.

  1. Conceptually, how should indexing be applied when using with Views and a JSONB array?
  2. What is the correct syntax to create the relevant indexes?
  3. Is the example view given the correct/optimal way to construct the view for this use case?

Example

Table

CREATE TABLE "ProductLists"
(
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    listName text NOT NULL
    productIds jsonb NOT NULL DEFAULT '[{ productId:0 }]'::jsonb,
)

View (can be changed)

With the following view:

SELECT "ProductLists".id AS listId,
    jsonb_array_elements("ProductLists".productIds) ->> 'productId'::text AS productId
   FROM "ProductLists";

Factors

  • The JSONB Root is an array, not an object (which is not the case in most indexing examples)
  • There will be potentially millions of ProductList items
  • The number of productIds in each list will usually be less than 100
  • The table will have both high reads and writes
  • The view SQL example may or may not be optimal for the purpose, and can be changed

Thanks for any input!

Improve this question
7
  • The most efficient way will be to normalize your data model. blog.2ndquadrant.com/… Commented Dec 11, 2019 at 7:12
  • Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… Commented Dec 11, 2019 at 7:12
  • What kind of queries are you planning to run on that table? What kind of conditions? Commented Dec 11, 2019 at 7:13
  • @a_horse_with_no_name, thanks for your response. Good tip on the quoted identifiers. RE: scenario & kind of queries. Basically, I need to maintain many orderd lists of products. The order matters. Previously I used mapping table with productId, listId and listOrder fields. However, the lists change frequently. A reordering operation for a long list can result in the need to update the listOrder value in 100's of rows. I am looking at using the JSONB field to avoid this. One ordered list, neatly stored in a single field. Commented Dec 11, 2019 at 10:06
  • @a_horse_with_no_name - (con't from above comment). The most frequenty query is simply getting productIds for a list. The JSONB solution works great here. However, an infrequent operation is to delete a product - in which case there is a need to identify all lists for a given productId. That is the useCase I am trying to solve here, by indexing the JSONB field, and adding a view Commented Dec 11, 2019 at 10:09

2 Answers 2

Reset to default
2

A GIN index on the jsonb column will support several JSON operators. One of them is the @> operator which also works with JSON arrays.

The following index:

create index on product_list using gin (product_ids);

A query that can potentially make use of the above index would look like this:

select *
from product_list
where product_ids @> '[{"productId": 42}]'::jsonb;

There is no way your suggested view can make use of an index as the JSONB column is not part of the view and that would be necessary to be able to push down the condition on the JSON column.

The only way you can make use of an index in the view is to include the JSON column in it:

create view normalized_list
as
SELECT pl.id AS list_id,
       t.product_id, 
       pl.product_ids
FROM product_list pl
   CROSS JOIN jsonb_array_elements(pl.product_ids) ->> 'id' AS t(product_id)
;

A query like this: 

select id, product_Id
from normalized_list
where product_ids @> '[{"id":42}]'::jsonb;

would make use of the GIN index.

Note that if you only want to store IDs in a denormalized way, a native integer array (product_ids int[]) would be more efficient and would make the table substantially smaller

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

Comments

1

https://www.postgresql.org/docs/current/datatype-json.html

{
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

-- Find documents in which the key "tags" contains key or array element "qui"
SELECT * FROM api WHERE jdoc -> 'tags' ? 'qui';

Still, with appropriate use of expression indexes, the above query can use an index. If querying for particular items within the "tags" key is common, defining an index like this may be worthwhile:

CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));

Now, the WHERE clause jdoc -> 'tags' ? 'qui'

will be recognized as an application of the indexable operator ? to the indexed expression jdoc-> 'tags'.

So if you will change structure from 

'[{ "productId":0 }, { "productId":0 }]'

to

'{"productIds": ["0", "1"]}'

or even

'{"products": {"0": {"title": "aaa"}, "1": {"title": "bbb"}}}'

you can build proper index

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.