Extracting information from a column of varchar type containing multiple json records

prativanayak

Hi

I have a table having a column of type varchar containing json records. The column can contain multiple json records per row. For the scenario when the column contains single json record, I am able to use mapjsonextractor, maplookup to read the data. However, for the scenario where there are multiple json records, mapjsonextractor throws warning. Is there a way to extract information for the multiple record scenario?

Example:
CREATE TABLE temp.fruits (id INTEGER, description VARCHAR(255));

INSERT INTO temp.fruits VALUES (1,'{"id": 1, "x": "Apples", "value": "128.14", "fill": "green"}');
INSERT INTO temp.fruits VALUES (2,'{"id": 2, "x": "Oranges", "value": "64.07", "fill": "orange"}');
INSERT INTO temp.fruits VALUES (3,'[{"id": 3, "x": "Oranges", "value": "64.07", "fill": "orange"},{"id": 4, "x": "Mangoes", "value": "64.07", "fill": "mango"}]');

select id, maplookup(mapjsonextractor(description),'id') from temp.fruits;
WARNING 3224: Error in calling destroy() for User Defined Scalar Function MapJSONExtractor at [/data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_3_1-x_grader/build/udx/supported/flextable/JSONExtractor.cpp:317], error code: 0, message: Rejecting row [3].

id | maplookup
----+-----------
3 |
1 | 1
2 | 2
(3 rows)

In the above example, for id=3, as the description column contains 2 json records, mapjsonextractor throws a warning.

Bryan_H

What is the expected result here? I would think you would expect ARRAY[3,4] though this is not yet supported for JSON types. We can take this as a feature request, but I would like to know what is the expected result, expected data type, whether order of results is significant.