Query Postgres Nested JSONB column using sequelize

Question

Hi I have a table where I use JSONB to store Nested JSON data and need to query this JSONB column Below is the structure of the table

 {
          "id": "5810f6b3-fefb-4eb1-befc-7df11a24d997",
          "entity": "LocationTypes",
          "event_name": "LocationTypes added",
          "data": {
            "event":{
            "id": "b2805163-78f0-4384-bad6-1df8d35b456d",
            "name": "builidng",
            "company_id": "1dd83f77-fdf1-496d-9e0b-f502788c3a7b",
            "is_address_applicable": true,
            "is_location_map_applicable": true}
          },
          "notes": null,
          "event_time": "2020-11-05T10:56:34.909Z",
          "company_id": "1dd83f77-fdf1-496d-9e0b-f502788c3a7b",
          "created_at": "2020-11-05T10:56:34.909Z",
          "updated_at": "2020-11-05T10:56:34.909Z"
        }

The code below is giving blank array as response


    const dataJson = await database.activity_logs.findAll({
          where: {
            'data.event.id': {
              $eq: 'b2805163-78f0-4384-bad6-1df8d35b456d',
            },
          },
          raw: true,
        });

Is there any way I can accomplish querying nested json object using sequelize in a better way .

Anatoly · Accepted Answer · 2023-01-24T17:22:41.470

5

You should try sequelize.literal with JSON-operators/functions wrapped into sequelize.where. Something like this:

sequelize.where(sequelize.literal("data->'event'->'id'"), '=', 'b2805163-78f0-4384-bad6-1df8d35b456d')

more recent syntax (not sure from what Sequelize version it starts to work) to construct conditions against JSON:

{
  data: {
    event: {
     id: 'b2805163-78f0-4384-bad6-1df8d35b456d'
    }
  }
}

edited Jan 24 '23 at 17:22

answered Nov 05 '20 at 16:49

Anatoly

20,799
3
28
42

Thanks for the reply @Anatoly , Sorry I have one more level inside data column its data.event.id not data.id . I have edited the question – naveen Nov 05 '20 at 16:55
is this how I should use ? const dataJson = await database.activity_logs.findAll( sequelize.where(sequelize.literal("data->'new'->'id'", '=', 'c44234ba-79a7-4d94-a378-7568dd4a8f5c')), ); – naveen Nov 05 '20 at 17:13
Almost. Don't forget to indicate `sequelize.where` in `where` option: `findAll({ where: sequelize.where(sequelize.literal("data->'new'->'id'", '=', 'c44234ba-79a7-4d94-a378-7568dd4a8f5c')) })` – Anatoly Nov 05 '20 at 17:14
i am getting the query like this when I use the above code SELECT "id", "entity", "event_name", "data", "notes", "event_time", "entity_id", "user_id", "client_id", "request_id", "source_ip", "company_id", "created_at", "updated_at" FROM "activity_logs" AS "activity_logs" WHERE data->'new'->'id' IS NULL; – naveen Nov 05 '20 at 18:13
My bad. Should be `sequelize.where(sequelize.literal("data->'new'->'id'"), '=', 'c44234ba-79a7-4d94-a378-7568dd4a8f5c')`. I updated the answer – Anatoly Nov 05 '20 at 18:19
thanks brother you helped me a lot .. +1 from my side.. not looking for this question ... but the solution solve my another problem ...I wasted my 3 hour..then I got the solution. – Abhishek Srivastava Nov 30 '22 at 10:23

Query Postgres Nested JSONB column using sequelize

1 Answers1