optimistic-gold

querying on non-primitive fields? (arrays?)

Wondering if this is possible right now -- I initially tried inArray, but it does the opposite of what I want, is column value within an array of values. i'd like 'does columns array of values contain value'

12 Replies

ambitious-aqua•3w ago

why doesn't inArray work again? it sounds like you're saying the same thing twice here in different ways

optimistic-goldOP•3w ago

basically a row is: inArray is { test: 1 } q.from().where(inArray(coll.test, [1]) // returns the row what I'd like: { test: [1,2,3] } q.from().where(whatIactuallyWant(coll.test, 1) // returns the row

ambitious-aqua•3w ago

so match against multiple items? you can OR

optimistic-goldOP•3w ago

but i'd have to split that into flat columns? i.e

{ test_0: 1, test_1: 2, test_3 }

{ test_0: 1, test_1: 2, test_3 }

then

q.from().where(
  or(
    inArray(coll.test_0, [1]),
    inArray(coll.test_1, [1]),
    // and so on...
)) // returns the row

q.from().where(
  or(
    inArray(coll.test_0, [1]),
    inArray(coll.test_1, [1]),
    // and so on...
)) // returns the row

? what I want, is basically this in postgres, when you query an array column:

SELECT * FROM your_table WHERE 'foo' = ANY(your_array_column);

SELECT * FROM your_table WHERE 'foo' = ANY(your_array_column);

btw thanks for all your help, you've answered like 3 of my questions 😅

ambitious-aqua•3w ago

@samwillis what are the options here?

optimistic-goldOP•6d ago

anything? @samwillis 🙏

national-gold•6d ago

Hey @moustacheful, flipping the arguments to inArray should work - so having a literal value (or a value from elesewhere) as the first arg, and then referencing the array prop as the second arg:

q
  .from(...)
  .where(
    ({ item }) => inArray('value i am checking for', item.arrayProp)
  )

q
  .from(...)
  .where(
    ({ item }) => inArray('value i am checking for', item.arrayProp)
  )

optimistic-goldOP•6d ago

oh wow that does work! I would never had imagined flipping those I imagine that's the extent of capabilities for now, right? no filtering from nested objects, correct?

national-gold•6d ago

By nested objects do you mean a prop on a row thats json / a javascript object? You can access any level of property on a column.

optimistic-goldOP•6d ago

oh. what about arrays of objects? so say you have a schema like

{
   array: [{username: 'foo'}]
}

{
   array: [{username: 'foo'}]
}

how would you structure the query in order to filter by the username?

sunny-green•6d ago

Just flatten it before you pass it. array: ( [{username: 'foo'}].map(x => x.username) )

optimistic-goldOP•6d ago

yeah ok that's what I'm doing right now. either way, thank you for your help!

Gaming

Programming

querying on non-primitive fields? (arrays?)

Did you find this page helpful?