Don't work when filtering on a json sub-property and using $in

[mdartic]

I'm trying to filter rows from a table where a filter is set on a sub-property of a jsonb field.

I have a model / service named row, where I have a data property that is a jsonb. In this data, I have some other fields, like this :

{
  id: 1,
  data: {
    mySubProperty: 10,
    myOtherSubProperty: {
      lastField: 20
    }
  }
}

I would like to filter on the lastField for example, or the mySubProperty.

I try a query like that :

{
  data : {
    mySubProperty: 10
  }
}

This is working.

But my goal is to use the $in.

So, I try this one :

{
  data : {
    mySubProperty: {
      $in: [10, 20]
    }
  }
}

This is not working.
By observing the debug logs, I can see the package take in account only the "last" property, that is to say mySubProperty, and don't think about the data field, which is specified in the row jsonSchema model as an object.

Same for the lastField property.

I've tried to update the code, and I think I find a way to update the code for making this work, and to make the objectify function able to remember the root field was an object one, so a jsonb field.

But, maybe there is a simple way that I didn't found.

If not, could you check the code here : mdartic@b5768cf

And tell me if it's a good way / idea ?
If it's ok, I could propose a PR ? (and I would like to add some tests, but don't know how to make them reliable)

[dekelev]

Thanks @mdartic, it looks good. Please open a PR with few tests. Testing only the $in method in a JSON object will suffice as long as you do not introduce breaking-changes and do take your time to think on more use cases and if this PR will address them too.

[mdartic]

I'm trying to add some tests, in 6.0.0, and that seems to be ok.

But in fact, when I use it in my project, with the 6.0.0, problem is still the same.

I expect the tests are not "ok", because we don't use under the hood a real PostgreSQL database.

I'm going to try to make the test working on a PgSQL DB, do you think it's a good idea ? I'm thinking it's the only way to be sure my code is really working as I would expect.

[dekelev]

You can add test that are specific to PG, just mention it in the mocha's describe and when done testing, commit it with skip. I have more tests there like that. בתאריך שבת, 17 באוק׳ 2020, 19:53, מאת Mathieu DARTIGUES ‏< [email protected]>:

…

I'm trying to add some tests, in 6.0.0, and that seems to be ok. But in fact, when I use it in my project, with the 6.0.0, problem is still the same. I expect the tests are not "ok", because we don't use under the hood a real PostgreSQL database. I'm going to try to make the test working on a PgSQL DB, do you think it's a good idea ? I'm thinking it's the only way to be sure my code is really working as I would expect. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#113 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABB5E3LQ754XUB2PTINST33SLHDZZANCNFSM4Q65RZCQ> .

[philipimperato]

Hmm, well json fields are not a functional feature if you can't query on it.

I would love to have known this before spending time moving stuff to json fields.