The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

prativanayakprativanayak Vertica Customer


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?

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_HBryan_H Vertica Employee Administrator

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file