Objection.js: Searching a jsonb array of objects that a key has a specific value

[aconran]

Given table defintion

await knex.schema.createTableIfNotExists('Person', table => {
                table.increments('id').primary();
                table.string('firstName');
                table.jsonb('emails').defaultsTo('[]').notNullable();
            });

and jsonSchema

                emails: {
                    type: 'array',
                    items: {
                        type: 'object',
                        properties: {
                            type: {type: 'string'},
                            value: {type: 'string'}
                        }
                    }
                }

I would like to get all emails that are of type "personal". Using raw SQL, I can do the following (found here https://stackoverflow.com/questions/28486192/postgresql-query-array-of-objects-in-jsonb-field)

Select * 
from "Person"
Where to_jsonb(array(select jsonb_array_elements(emails) ->> 'type')) ?| array['personal']

Is there any way to do this with the whereJson* methods of Query builder?

[aconran]

Or a bit prettier (from https://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type/22737710#22737710)...

Select *
from "Person"
Where emails @> '[{"type": "personal"}]'

[koskimas]

Maybe

Person
  .query()
  .whereJsonSupersetOf('emails', [{type: 'personal'}])

?

[elhigu]

I would like to get all emails that are of type "personal".

Those queries mentioned doesn't extract emails of type personal, but actually returns those persons, who has at least one email of type personal.

As far as I know postgres does not have any easy way for filtering what kind of data is fetched from inside of a jsonb column. So it is always pretty much just one attribute of the json column that can be selected to be fetched, except if you first extract mails to temporary table or so and use normal sql filtering to get those mails of specific type.

But I suppose you actually want all the persons having at least on personal email and like @koskimas mentioned .whereJsonSupersetOf is good for that. In recipe book there is some guides how to create indexes supporting that operation to make the query reasonably fast.

[aconran]

That's what I was looking for, thanks!

[vampyar]

Maybe

Person
  .query()
  .whereJsonSupersetOf('emails', [{type: 'personal'}])

?

Select *
from "Person"
Where emails @> '[{"type": "personal"}]'

It's not same, because you code generate query like

Select * 
from "Person"
where ("emails" #>'{}')::jsonb <@ '[{"type": "person"}]'

For this case I'm create modify, but I think it's not best idea

class Person {
  static modifiers = {
     whereJsonFieldArray: (qb: QB, args: object[]) =>
        qb.whereRaw(`"${args.field}"  @> '${JSON.stringify(args.values)}'`)
  }
}

and using like:

Person
  .query()
  .modify('whereJsonFieldArray', {
     field: 'person',
     values: [{type: "person"}]
  })

Maybe you have @koskimas any other idea for this case or add some method to objection.js ?

[fiznool]

I hit upon the same issue as @vampyar today and submitted a new issue #2008