Using `in` or `is_nil()` for a jsonb property

Ash Forum Ash Questions / Help
, ,
1

I’m trying to understand the Ash filter expression syntax for working with json arrays within a jsonb column in ash_postgres.

I have a jsonb column, settings, on a table notification_settings

For a simple equality expression, I can use the == operator:

NotificationSettings
|> Ash.Query.filter(settings["foo"] == "bar")
|> Ash.read!(authorize?: false)

But for a value in enum expression:

NotificationSettings
|> Ash.Query.filter("email" in settings["dues_reminders"])
|> Ash.read!(authorize?: false)

It produces:

** (Ash.Error.Unknown)
Bread Crumbs:
  > Error returned from: NotificationSettings.read
Unknown Error
* ** (Postgrex.Error) ERROR 22P02 (invalid_text_representation) malformed array literal: "["email"]". If you are trying to query a JSON field, the parameter may need to be interpolated. Instead of
    p.json["field"] != "value"
do
    p.json["field"] != ^"value"
"[" must introduce explicitly-specified array dimensions.

So I have to use fragment() instead:

NotificationSettings
|> Ash.Query.filter(fragment("?->'dues_reminders' \\? ?", settings, "email"))
|> Ash.read!(authorize?: false)

A very similar thing happens with is_nil() on a jsonb property:

NotificationSettings
|> Ash.Query.filter(is_nil(settings["dues_reminders"]))
|> Ash.read!(authorize?: false)

It produces:

* ** (Postgrex.Error) ERROR 22P02 (invalid_text_representation) malformed array literal: "["email"]"...

For that too, I must use fragment instead:

NotificationSettings
|> Ash.Query.filter(fragment("?->'dues_reminders' is null", settings))
|> Ash.read!(authorize?: false)

Is this correct? Is there no built-in syntax for doing jsonb value comparisons beyond the == operator?

2

I believe this is a bug. Please open an issue and I will investigate :slight_smile:

3

Interesting :thinking: I’m getting different behavior.

    Author
    |> Ash.Query.filter(is_nil(settings["optional_field"]))
    |> Ash.read!()

works, but:

    Author
    |> Ash.Query.filter("email" in settings["dues_reminders"])
    |> Ash.read!()

throws a different error:

* ** (Postgrex.Error) ERROR 42809 (wrong_object_type) op ANY/ALL (array) requires array on right side

What version of postgres are you on?

4

Thanks for looking into this!

Here’s my Postgres version: PostgreSQL 14.17 (Homebrew) on aarch64-apple-darwin24.2.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.6), 64-bit

That version is close to my production database (14.19).

Going off of your example, the SQL generated from the value in jsonb_column[“array”] case looks something like this:

SELECT 
  s."id"
FROM authors AS s
WHERE
  'email' = any(
    jsonb_extract_path_text(s.settings::jsonb, 'dues_reminders'::text)::varchar[]
  )
;

If I set up a test table and record like this:

CREATE TABLE authors (
  id uuid PRIMARY KEY,
  name varchar,
  settings jsonb
);

insert into authors (id, name, settings) values (gen_random_uuid(), 'one', '{"dues_reminders": ["email"]}');

Then run that query, it produces:

-- ERROR:  malformed array literal: "["email"]"
-- DETAIL:  "[" must introduce explicitly-specified array dimensions.

But the critical part is: it only produces an error if there is a record that has a ”dues_reminders” property in the jsonb object. If there’s no record with that property, there’s no error.

Additionally, if I remove the ::varchar[] cast at the end of that jsonb_extract_path_text() expression, it produces a different error:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 5:   'email' = any(

That’s similar to the error you got.

I think maybe jsonb_extract_path_text(jsonb, txt)::varchar[] is using an incompatible casting (::varchar[]), and ’value’ = ANY() isn’t the right expression for this. jsonb_extract_path_text() returns stringified JSON ([“email”]), and the ::varchar[] cast wants a PostgreSQL array ({“email”}).

This expression works:

SELECT 
  s."id",
  jsonb_extract_path_text(s.settings::jsonb, 'dues_reminders'::text)::jsonb ? 'email'
FROM authors AS s

I think that’s what ash_postgres needs to generate.

5

For the is_nil(jsonb_column[“property”]) case, this SQL works:

SELECT 
  s."id"
FROM authors AS s
WHERE
  jsonb_extract_path_text(s.settings::jsonb, 'other'::text)::jsonb IS NULL
;

The difference between this and what ash_postgres is generating is replacing the ::varchar[] cast with ::jsonb[].

6

So, I still haven’t managed to reproduce the issue with is_nil. Perhaps you could PR a test case to ash_postgres with that behavior? For the in case I’ve fixed that in ash_sql main :smiley:

7

Thanks @zachdaniel !

I created an ash_postgres PR that reproduces the is_nil() issue: Demonstrate failed query for is_nil on array within jsonb by moxley · Pull Request #615 · ash-project/ash_postgres · GitHub