Select entries with empty array field

Stefan Pointecker

Hi,

I have a table which has a field "tags" which is a list of tags from another table.

Now I would like to have a query which loads all entries without any tag (tag count = 0)

I tried it with a length filter and a condition to 0:

Unfortunately this throws the following error:

Whoops! An error occurred.

Exception: SQLSTATE[22023]: Invalid parameter value: 7 ERROR: cannot get array length of a scalar (SQL: select "study_program".* from "mvpw2_17" as "study_program" where jsonb_array_length("study_program"."xdo"->'tags') = 0::bigint

Field config:

Michael Udinski

Hey @Stefan Pointecker since you are dealing with an array in the database table, you will want to set the expression equals to an array. You can use an empty array to find your results:

WHERE

db: study_program.tags = array: []

Stefan Pointecker

Hey @Michael Udinski

I tried that but it failed too:

ParseError: "study_program.tags" = [], message=Lists are not supported.

Ray Deck

Instead of looking explicitly for an empty array, you can use a filter called "length" on the left-hand-side and check to see whether the resulting value is 0. Because an empty array has zero elements!

Stefan Pointecker

@Ray Deck that is what I initially did, see my first posting.

Ray Deck

Indeed it was! I ran the same filter on a test table in my instance and it worked, so I wonder what else might be true about your query or table that is related to the problem. If you share more glad to look.

I also see someone else has a similar issue on this forum atm so maybe running into a product issue or something in your instances.

Michael Udinski

Hey @Stefan Pointecker @Ray Deck just confirmed setting it to an empty array is throwing an error. But also confirmed using the length filter works as expected. Stefan - I see your expression looks bigger than just the one mentioned. Can you please write into support chat regarding the error you are experiencing?

Stefan Pointecker

Hey @Michael Udinski,

there was just one other condition (published = true) - I removed that and the same error occurs. Writing now to chat as suggested, thank you!