SQLParseException while importing JSON

[Michele]

Hello

This query leads to the error below; in other words, is it possible to import any json to a table created as “create table msg (o object);” ?

INSERT INTO msg VALUES ('{"entry":[{"changes":[{"field":"messages","value":{"contacts":[{"profile":{"name":"Name Surname"},"wa_id":"20000000301"}],"messages":[{"from":"20000000301","id":"wamid.da0dcd656790480cb19bb8d20a31401b","referral":{"ctwa_clid":"23a2d720084042bfaa2b3d927c3caf4d","source_id":"120000000000000000","source_url":"https:\/\/fb.me\/ABC"},"text":{"body":"Hello, I want to buy flowers with a 10% discount."},"timestamp":"1724779122","type":"text"}],"messaging_product":"whatsapp","metadata":{"display_phone_number":"10000000301","phone_number_id":"100000000000000"}}}],"id":"100000000000000"}]}'::object)

crate.client.exceptions.ProgrammingError: SQLParseException[Cannot cast object element `entry` with value `[{changes=[{field=messages, value={metadata={phone_number_id=100000000000000, display_phone_number=10000000301}, messaging_product=whatsapp, messages=[{referral={source_id=120000000000000000, ctwa_clid=23a2d720084042bfaa2b3d927c3caf4d, source_url=https://fb.me/ABC}, from=20000000301, id=wamid.da0dcd656790480cb19bb8d20a31401b, text={body=Hello, I want to buy flowers with a 10% discount.}, type=text, timestamp=1724779122}], contacts=[{profile={name=Name Surname}, wa_id=20000000301}]}}], id=100000000000000}]` to type `object_array`]```

[proddata]

What version of CrateDB are you running?
Did you insert another value into that table before?

The reported queries seem to work for me on a local 5.8.1 cluster

cr> CREATE TABLE msg (o OBJECT);
    INSERT INTO msg VALUES ('{"entry":[{"changes":[{"field":"messages","value":{"contacts":[{"profile":{"name":"Name Surname"},"wa_id":"20000000301"}],"messages":[{"from":"2000000030
    1","id":"wamid.da0dcd656790480cb19bb8d20a31401b","referral":{"ctwa_clid":"23a2d720084042bfaa2b3d927c3caf4d","source_id":"120000000000000000","source_url":"https:\/\/fb.me\/ABC"},
    "text":{"body":"Hello, I want to buy flowers with a 10% discount."},"timestamp":"1724779122","type":"text"}],"messaging_product":"whatsapp","metadata":{"display_phone_number":"10
    000000301","phone_number_id":"100000000000000"}}}],"id":"100000000000000"}]}'::object);
CREATE OK, 1 row affected (0.102 sec)
INSERT OK, 1 row affected (0.020 sec)

edit:
oh … I see the same error in the cloud console … needs investigation

edit 2:
I assume this is related from some sort of copying error and the json containing a line break or whitespace at an unfortunate position.

[Michele]

Edit: I recreated the table and it works;

Thanks for the reply @proddata
I am trying with a free cluster on CrateDB, and I’m oddly still getting it by pasting your query: