Is crateDB support mixed array type insertion and querying of the column

CrateDB
1

Dear Team

I had performed a test using mixed array type object of ignored case. I have found that the value of the column index disappeared for index position 2.

Steps to reproduce:

CREATE TABLE t (an_array_attr OBJECT(ignored));
INSERT INTO t (an_array_attr) VALUES ( '{"value": ["wada", ["wada", false], 3]}'::object );

Here, ["wada", false] disappeared in the test result. It only shows the "wada" and 3 in the output.

So, i only want to know about this unusual behavior of crate db.
Is it a cratedb bug or cratedb not support the mixed array type?

If cratedb does not support the mixed array type than, how could the below test succeed:

INSERT INTO t (an_array_attr) VALUES ( '{"value": ["yo!", true]}'::object ); 
INSERT INTO t (an_array_attr) VALUES ( '{"value": [{"k": 1}, 2]}'::object );

These test cases are failed in case of dynamic insertion.

Kindly help me to understand the exact behavior.

2

Dear @Ajaysamdyan,

thank you for writing in. To give the community more context about your question, I would like to add the backlink to Typed Arrays · Issue #147 · orchestracities/ngsi-timeseries-api · GitHub. We made friends with the people conceiving FIWARE QuantumLeap the other day already. Cheers!

With kind regards,
Andreas.

3

Hi @Ajaysamdyan

CREATE TABLE t (an_array_attr OBJECT(ignored));
INSERT INTO t (an_array_attr) VALUES ( '{"value": ["wada", ["wada", false], 3]}'::object );

By

Here, [“wada”, false] is disappeared in test result.

are you refering to the admin ui?
It seems like the Admin UI indeed has a bug, but the data should still be there (turned off formatting)

image
image1106×692 25.1 KB
image
image1038×814 27.5 KB

With OBJECT(IGNORED) you could store pretty much any kind of JSON that is encapsulated on the top-level in curly brackets

4

Hi prodata, All the queries are executed by superuser “crate”, and only ["wada",false] is missing.

5

How are you checking if the values are missing. I tried to replicate this and can see, that the Admin UI has a display bug together with formatting. However, when turning formatting off, I still can see ["wada",false].

Also using other clients, I can see that the array is still available.

1 Like
6

Hi prodata, which version you are using of CrateDB. i have tested it on CrateDB 4.1.4 and 4.5.1.
Please provide me any reference like link, document etc. on how to turn off formatting.

7

I just tested it with 4.6.3 / 4.5.1 / 4.3.4

The formatting can be turned of with that checkbox marked in yellow:

image
image1096×1050 43.6 KB

8

Hi @prodata, yes you are right. So basically its a bug right, it should have to shows the data in format style as well?

3 Likes
9

I will see if this can be easily fixed and otherwise create a bug report. Thanks for making us aware about the issue :pray:

2 Likes
10

Hi again,

thank you for figuring this out so quickly. I am happy it turned out to be “only” a bug in Admin UI. Nevertheless, as many people, including you, will go to the Admin UI for their first steps using and evaluating CrateDB, we will try to come up with a bugfix as soon as possible.

In order to track this issue, I just created Display bug with "format results" · Issue #744 · crate/crate-admin · GitHub.

With kind regards,
Andreas.

1 Like
11

Dear @Ajaysamdyan,

the CrateDB 4.6.4 hotfix release will include Admin UI 1.19.2, which includes the fix Added ability to correctly display nested arrays by proddata · Pull Request #745 · crate/crate-admin · GitHub by @proddata – thanks a stack!

The Docker image is already available through crate/crate:4.6.4.

With kind regards,
Andreas.