Repository navigation
Nested JSON insert strict #248
Description
Activity
👋 @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.querywith 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}}] #=> ]
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:
With that, I want to be able to insert any JSON data, but for
attributes.timestampit should be parsed as UInt64. I've been using this to insert data using the Node Clickhouse client, withJSONEachRow, and it works as expected.Now, using
ecto_ch, I get this error:I added the sample test code here: https://github.com/fabiokr/ecto_ch/blob/nested-json/test/ecto/integration/json_nested_test.exs
ecto_chusesBinaryRowInputFormat, which seems to be more strict. Is there a way around this? Could we possibly allow insert usingJSONEachRowinstead?