Fetching from storage object based on custom json value

Local Setup
1

Hello!

I’m trying to find a set of storage objects based on a custom value in the json db. I know this was asked before in the chat but would love to have the example code!

2

Hey @Superxwolf,

Just to expand on what you’ve asked for:

You are asking to look up other database rows based on a value that is stored inside the “value” field. As the server only accepts JSON values in the Storage engine, this would ideally be a set of fields in the JSON object stored.

To do this, we’ll need to use various JSONB operators available in Postgres. This blog post shows a few of the queries along side examples:

https://hackernoon.com/how-to-query-jsonb-beginner-sheet-cheat-4da3aa5082a3

And the main Posgres documentation also shows it well:

https://www.postgresql.org/docs/11/functions-json.html

In case you are using CockroachDB, refer to this documentation:

https://www.cockroachlabs.com/docs/stable/jsonb.html
https://www.cockroachlabs.com/docs/stable/demo-json-support.html

Important If you’ll be using JSONB operators for common queries, I highly suggest that you look into GIN indexes:

https://www.postgresql.org/docs/11/gin.html
https://www.cockroachlabs.com/docs/stable/inverted-indexes.html

If you give me a specific data structure, I can put together a complete example for you.

1 Like
3

Hello @mofirouz,

Thanks for the in-depth answer and articles. I’ll look into them to ensure performance with indexes and other future queries I’ll need.

As for my data structure, it is as follow:

    {
       collection: "Equipments",
       key: 1, // Equipment ID 
       value:
          {
              id: 1,  // Equipment ID
              rarity: 3,  // Rarity from 0 (Most Common) to 10 (Legendary)
              
              // Other stats
          },
       user_id: nil // Using public records
    }

The goal is to find all equipments within a specific range of rarity, like all equipments of rarity 4 to 6.

4

Here is the example:

SELECT collection, key, user_id, value FROM storage;

  collection | key |               user_id                |         value
+------------+-----+--------------------------------------+------------------------+
  Equipments | 1   | 00000000-0000-0000-0000-000000000000 | {"id": 1, "rarity": 3}
  Equipments | 2   | 00000000-0000-0000-0000-000000000000 | {"id": 1, "rarity": 5}
  Equipments | 3   | 00000000-0000-0000-0000-000000000000 | {"id": 1, "rarity": 1}
  Equipments | 4   | 00000000-0000-0000-0000-000000000000 | {"id": 1, "rarity": 6}
  Equipments | 5   | 00000000-0000-0000-0000-000000000000 | {"id": 1, "rarity": 7}
(5 rows)

The following query filters based on the values inside the value field:

SELECT collection, key, user_id, value FROM storage WHERE cast(value->>'rarity' AS int) >= 4 AND cast(value->>'rarity' AS int) <= 6;

  collection | key |               user_id                |         value
+------------+-----+--------------------------------------+------------------------+
  Equipments | 2   | 00000000-0000-0000-0000-000000000000 | {"id": 1, "rarity": 5}
  Equipments | 4   | 00000000-0000-0000-0000-000000000000 | {"id": 1, "rarity": 6}
(2 rows)
5

Don’t forget it’s highly recommended to create indexes for these specific fields that you’ll query on within your storage objects. The server is designed to be customized in this way but cannot guess the indexes that would need to be added for custom query patterns like these.

1 Like