path doesn't handle nil value

[carlost]

Tell us about your environment

Ruby Version: ruby 2.7.4p191 (2021-07-07 revision a21a3b7d23) [x86_64-darwin19]

Rails Version: rails (6.1.4.1)

PostgreSQL Version: 11.13

PgRel Version: pgrel (0.3.1)

What did you do?

Jsonb.where.store(:tags).path(:p, nil).pluck(:name)

Assuming:

Jsonb.create!(name: "a")
Jsonb.create!(name: "b", tags: {a: 1, d: {e: 2}})
Jsonb.create!(name: "c", tags: {a: 2, b: %w[c d]})
Jsonb.create!(name: "d", tags: {a: 1, b: {c: "d", e: true}})
Jsonb.create!(name: "e", tags: {b: 2, c: "e"})
Jsonb.create!(name: "f", tags: {d: {e: 1, f: {h: {k: "a", s: 2}}}})
Jsonb.create!(name: "g", tags: {f: false, g: {a: 1, b: "1"}, h: [1, "1"]})
Jsonb.create!(name: "z", tags: {z: nil})
Jsonb.create!(name: "p_nil", tags: {p: nil})
Jsonb.create!(name: "p_empty", tags: {p: ''})
Jsonb.create!(name: "p_value", tags: {p: 'hello'})

What did you expect to happen?

I expected it to return: ["p_nil"]

What actually happened?

It actually returned: ["p_empty"]

ActiveRecord::QueryMethods::JsonbChain#path converts a nil to a string value ... which results in a value of ''. This generates the following sql for the example above:

SELECT "jsonbs"."name" FROM "jsonbs" WHERE "jsonbs"."tags"#>>'{p}' = '';

I think there are a few different ways to handle this depending on what the expected behavior of path with a nil value should be:

• should it only match when value is null in JSON or,
• should it match when value is null AND when the path doesn't exists

If ONLY when value is null:

the case stmt in ActiveRecord::QueryMethods::JsonbChain#path can be updated to:

        case val
        when Hash
          op = "#>"
          val = ::ActiveSupport::JSON.encode(val)
        when Array
          op = "#>>"
          val = val.map(&:to_s)
        # only null value in json
        when NilClass
          op = "#>"
          val = "null"
        else
          op = "#>>"
          val = val.to_s
        end

If when value is null AND when the path doesn't exists

the case stmt in ActiveRecord::QueryMethods::JsonbChain#path can be updated to:

        case val
        when Hash
          op = "#>"
          val = ::ActiveSupport::JSON.encode(val)
        when Array
          op = "#>>"
          val = val.map(&:to_s)
        # null value and missing key in json
        when NilClass
          op = "#>>"
        else
          op = "#>>"
          val = val.to_s
        end

[palkan]

I think, we should go with

should it only match when value is null in JSON

The presence of nil is not the same as the absence of the key. For the absence, we need something like store(:tags).not.path(:a, :b) (doesn't work this way right now).

Would you like to propose a PR?