Effectively query range of arbitrary data stored in json array

Question

I am trying to query data stored in an array in a field of type jsonb as follows:

Data description:

There is a table named items which has 4 columns: name, owner, seller, and metadata (metadata is the column that I want to query on). The data in metadata looks like this:

metadata {

  ...
  "attributes": [
    {
      "trait_type": "rareity"
      "value": "ultra-rare",
    },
    {
      "trait_type": "color"
      "value": "red",
    },
    {
      "trait_type": "attract"
      "value": 6,
    },
    {
      "trait_type": "defend"
      "value": 5,
    },
    ...
  ],
  ...
}

There are usually many pairs of trait_type and value in attributes , a attributes in level 1 of metadata column. Pairs in attributes can be added arbitrarily. For example, I can add pair { "trait_type": "material", "value": "silk" } into attributes in example above.

Question:

I want to perform a mix of multiple string matching searches and number in range searches. For example: trait_type = material and 10 < defend < 20 and attract > 5.

I have thought of separating attributes into a table with 2 colums trait_type and value and querying SQL using intersect but when adding more conditions, I have to add more intersect also and It is very ineffective.

Is there any table design, indexing strategy, tools... so I can effectively query that kind of data?

SQLpro · Accepted Answer · 2022-09-30 11:11:42Z

1

As long as the free version of PostGreSQL will be unable to have some "in memory" features like Microsoft SQL Server, there is no proper way to ensure performances for those type of queries. The usual way to do that is to put the data in a key/value pair table in memory like Redis or some other specialized NoSQL DBMS do.

answered Sep 30, 2022 at 11:11

SQLpro

5,3121 gold badge9 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Effectively query range of arbitrary data stored in json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related