Fetching from storage object based on custom json value


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!

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:


And the main Posgres documentation also shows it well:


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


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


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

1 Like

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 
              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.

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)

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