Nested JSON insert strict

[fabiokr]

This is my use case:

I have a table which has a JSON column. For some of the possible JSON values, I want to enforce a specific type. This is an example:

CREATE TABLE json_nested
      (
          `user_id` UInt32,
          `data` JSON(attributes JSON(timestamp UInt64))
      )
      ENGINE = MergeTree
      PRIMARY KEY (user_id)
      ORDER BY (user_id)
      SETTINGS index_granularity = 8192
      ;

With that, I want to be able to insert any JSON data, but for attributes.timestamp it should be parsed as UInt64. I've been using this to insert data using the Node Clickhouse client, with JSONEachRow, and it works as expected.

Now, using ecto_ch, I get this error:

** (Ch.Error) Code: 117. DB::Exception: Type of 'data' must be JSON(attributes JSON(timestamp UInt64)), not JSON: (while reading header): While executing BinaryRowInputFormat. (INCORRECT_DATA) (version 25.9.3.48 (official build))

I added the sample test code here: https://github.com/fabiokr/ecto_ch/blob/nested-json/test/ecto/integration/json_nested_test.exs

ecto_ch uses BinaryRowInputFormat, which seems to be more strict. Is there a way around this? Could we possibly allow insert using JSONEachRow instead?

[ruslandoga]

👋 @fabiokr

Type of 'data' must be JSON(attributes JSON(timestamp UInt64)), not JSON

Might be fixed if we pass the "full" JSON type name instead of just JSON. Right now we discard all of the JSON options and don't send them.

Could we possibly allow insert using JSONEachRow instead?

It's possible, but a bit of a hack -- you can use Repo.query with a custom format:

Repo.query!("""
CREATE TABLE json_nested
(
  `user_id` UInt32,
  `data` JSON(attributes JSON(timestamp UInt64))
)
ENGINE = MergeTree
PRIMARY KEY (user_id)
ORDER BY (user_id)
SETTINGS index_granularity = 8192
""")

sql = "insert into json_nested format JSONEachRow\n" # \n is important

rows = [
  %{user_id: 1, data: %{"attributes" => %{"timestamp" => 1234567890}}},
  %{user_id: 2, data: %{"attributes" => %{"timestamp" => 1234567891}}}
]

data = Enum.map_intersperse(rows, ?\n, fn row -> JSON.encode_to_iodata!(row) end)
query = [sql | data]

%Ch.Result{command: :insert, num_rows: 2} =
  Repo.query!(query, _no_params = [], encode: false)

Repo.query!("select * from json_nested").rows
#=> [
#=>   [1, %{"attributes" => %{"timestamp" => 1234567890}}],
#=>   [2, %{"attributes" => %{"timestamp" => 1234567891}}]
#=> ]