How to insert a bare null jsonb value to Postgres table field in Ruby on Rails via ActiveRecord?

Question

Here is an SQL snippet:

CREATE TABLE jsonb_null (
  id bigserial PRIMARY KEY,
  value jsonb
);
INSERT INTO jsonb_null (value) VALUES ('"null"'), ('null'), (NULL);
SELECT id, value, jsonb_typeof(value) FROM jsonb_null ORDER BY id;

which outputs the following:

 id | value  | jsonb_typeof
----+--------+--------------
  1 | "null" | string
  2 | null   | null
  3 |        |
(3 rows)

I want the option (2) where there's a bare null jsonb value. How can I insert this?

Here is a failing example if we use ActiveRecord to select a value. It is not necessarily related to inserting but shows the problem.

require 'bundler/inline'

gemfile(true) do
  source 'https://rubygems.org'
  git_source(:github) { |repo| "https://github.com/#{repo}.git" }
  gem 'rails'
  gem 'pg'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

# `CREATE DATABASE rails_test;`
ActiveRecord::Base.establish_connection(adapter: 'postgresql', database: 'rails_test')
ActiveRecord::Base.logger = Logger.new($stdout)
ActiveRecord::Base.connection.execute(<<~SQL)
  DROP TABLE IF EXISTS jsonb_nulls;
  CREATE TABLE jsonb_nulls (
    id bigserial PRIMARY KEY,
    value jsonb
  );
  INSERT INTO jsonb_nulls (id, value) VALUES (1, '"null"'), (2, 'null'), (3, NULL);
SQL

class JsonbNull < ActiveRecord::Base; end

class JsonbNullTest < Minitest::Test
  def test_jsonb_null
    refute_equal JsonbNull.find(2).value, JsonbNull.find(3).value
  end
end

Are you able to provide an MWE which demonstrates the issue you are having? — w08r
– w08r, Commented Apr 3, 2024 at 8:17
@w08r not really, but I added a test case for null blindness when selecting values — Dmitry Matveyev
– Dmitry Matveyev, Commented Apr 3, 2024 at 9:55
INSERT INTO jsonb_nulls (id, value) VALUES (1, '"null"'), (2, 'null'), (3, {}); — dbugger
– dbugger, Commented Apr 3, 2024 at 11:12
@dbugger if I understand correctly, you propose to codify the null value as {}. This is a fine alternative, unless one actually needs the empty hash value. I know that this can be applied universally like 0 for integers, "" for strings, [] for arrays and {} for hashes. At the same time I would like to avoid hacks and just use what Postgres provides. — Dmitry Matveyev
– Dmitry Matveyev, Commented Apr 3, 2024 at 11:18

max · Accepted Answer · 2024-04-04 16:58:26Z

4

While Postgres has different types for SQL null and JSONB null the Ruby database driver doesn't and will cast both into nil which is the only type of nothing built into the language.

That's why the test fails and should be expected to fail.

class JsonbNullTest < Minitest::Test
  def test_jsonb_null
    assert
      JsonbNull.find(2).value, 
      JsonbNull.find(3).value,
      "Nil will always equal nil"
  end
end

If you want to query for JSONB null use jsonb_typeof(column) = 'null'. This is not a query that you can generate with the query interface but you can do it with a SQL string or Arel.

JsonbNull.where("jsonb_typeof(value) = 'null'")

To create a record with a bare JSONB null value with the normal ActiveRecord methods such as create is going to be exceptionally hard.

ActiveRecord really goes out of its way to keep you safe from your own stupidity and will escape anything you throw at to avoid potential SQL injection attacts. This even includes types such as Arel::Nodes::SqlLiteral which are generally treated as trusted by the query methods.

irb(main):013:0> JsonbNull.create!(value: Arel.sql("'null'::jsonb"))
  TRANSACTION (0.2ms)  BEGIN
  JsonbNull Create (0.4ms)  INSERT INTO "jsonb_nulls" ("value", "created_at", "updated_at") VALUES ($1, $2, $3) RETURNING "id"  [["value", "\"'null'::jsonb\""], ["created_at", "2024-04-03 13:39:50.625036"], ["updated_at", "2024-04-03 13:39:50.625036"]]
  TRANSACTION (3.1ms)  COMMIT
=>
#<JsonbNull:0x00007f7ee885edc8
 id: 9,
 value: "'null'::jsonb",
 created_at: Wed, 03 Apr 2024 13:39:50.625036000 UTC +00:00,
 updated_at: Wed, 03 Apr 2024 13:39:50.625036000 UTC +00:00>

To acheive this you would need to use the lower level methods and constuct the insert statement yourself.

I would consider if there is an easier way of reaching your goal like just using SQL null or not using JSONB.

edited Apr 4, 2024 at 16:58

answered Apr 3, 2024 at 13:36

max

103k15 gold badges113 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

engineersmnky Over a year ago

So I did a little tracing and yes this would be complicated to "fix" because AR ignores nil, so that the insert/update can be NULL for SQL. If overriding this functionality is acceptable e.g. the jsonb column would always show json null instead of SQL NULL it appears you could patch this into the Postgres::OID::Jsonb class by defining serialize and modifying PostgreSQL::Quoting::ClassMethods::quote.

max Over a year ago

@engineersmnky that seems like something that could have some unexpected side effects down the road.

engineersmnky Over a year ago

I totally agree and I am in no way endorsing it as I have not tested the theory to any capacity. It was more an exercise in the capability to perform such an action, not the functionality or practicality of actually implementing it.

Collectives™ on Stack Overflow

How to insert a bare null jsonb value to Postgres table field in Ruby on Rails via ActiveRecord?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related