[Q] query service with property in array of objects jsonb

[noor-tg]

- how to query service rows with property in array of objects in jsonb field from the client ?

- what are the needed flags to be whitelisted in the service options ?

I use :

• postgresql: 12.2
• objectionjs: 2.2.3
• feathers-objection: 5.7.0

for example

let item = {
    category: "close",
    stores: [
         { id: 1, name: "some store", quantity: 150}
    ]
}

let res = await service("items").find({
   query: {
       'stores:name': "some store",
       $select: ['category', "stores"]
   }
})

---

- how to query by $like, $in, $gt ...... for property in array of objects ?

I need to ask if feathers objection provide some thing like mongodb subdocument query

[dekelev]

Hi @Nour-DEV, I see ObjectionJS already had a discussion about this in Vincit/objection.js#1133

If you know the item index in the array you want to query, then check the examples here for how to query a JSON field.

Using $allowRefs & $select with ref, you can select specific keys from a JSON field.

[noor-tg]

If you know the item index in the array you want to query, then check the examples here for how to query a JSON field.

I do not know the index. so how to query it ?

Using $allowRefs & $select with ref, you can select specific keys from a JSON field.

can you show me some example code ?

I see ObjectionJS already had a discussion about this in Vincit/objection.js#1133

I will check it
thank you

[dekelev]

You can find examples in the README and in the test file

[noor-tg]

ok . thank you @dekelev

[devashishsethia]

@Nour-DEV - I'm struggling with the same issue. If you got that working, could you please share how?
e.g. I have the following column:

"rideEventsArray": [
                {
                    "name": "Testuser126",
                    "type": "pickup",
                    "isDone": false,
                    "number": "919999999126",
                    "status": "awaiting_pickup",
                    "address": "B-101, Siddhi Vinayak Nagar, Mahajan Wadi, Mira Road, Mira Bhayandar, Maharashtra 401107, India",
                    "riderId": 126,
                    "location": {
                        "latitude": 19.2683723,
                        "longitude": 72.8724951
                    },
                    "avatarURL": null,
                    "isCancelled": false
                },
                ....
            ]

I want to query if there is an entry with a specific riderId and status, so I don't know the index. Thanks in advance.

[noor-tg]

@devashishsethia

async fetch() {
  const searchId = 126
  const searchstatus = 'awaiting_pickup'
  const {data} = await api.service('service-name').find({
    query: {
      jsonArrayField: {$contains: {id: searchId, status: searchStatus}}
    }
  })
}

if you use this library from client you must whitelist the $contains query parameter
note: this only work with direct values id does not work with partial searches for example with $like parameter

[devashishsethia]

This, unfortunately, didn't work. I have resorted to creating a modifier instead. I don't know whether it's going to have to any performance impact, but I can live with it for a while.

[noor-tg]

This, unfortunately, didn't work. I have resorted to creating a modifier instead. I don't know whether it's going to have to any performance impact, but I can live with it for a while.

why ? is there any error show in the logs or in the console ? or the data set returned was empty ?

[devashishsethia]

The data didn't get filtered. When I used the (0).objectField notation as in the docs, that worked but I really didn't know the index at which the search criteria would occur.

[noor-tg]

The data didn't get filtered. When I used the (0).objectField notation as in the docs, that worked but I really didn't know the index at which the search criteria would occur.

check the original objectionjs json filtering functions and check the feathers-objection source code . you will find the correct code to use the $contains parameter.
I did used it before . but I do not know the syntax.