Create nested json in postgresql-CodePudding

I'm trying to create JSON object by passing to json_object() two arrays. One with keys, second with JSON(or JSONB) objects. So as to put JSON as value for predefined key. The signature of json_object() function is json_object(varchar[], varchar[]) but in my case I have json_object(varchar[], json[]). The issue is when I'm typecasting json_object(varchar[], json[]::varchar[]) stringification creates escaped string for json element and postgress does not recognize value as nested JSON, but instead thinks that is string value.

SELECT
   json_object(
                ARRAY[set_of_keys::varchar],
                ARRAY[set_of_values::json],
              ) as some_column

gives error function json_object(varchar[], json[]) does not exist

SELECT
   json_object(
                ARRAY[set_of_keys::varchar],
                ARRAY[set_of_values::json]::varchar[],
              ) as some_column

lead to esacaped stringification of set_of_values.

How to put these objects as a value without stringification?

Sample data:

SELECT
   json_object(
                ARRAY['a', 'b', 'c'],
                ARRAY['{"key1":"value1"}'::json, '{"key2": "value2"}'::json, '{"key3": "value3", "key4":"value4"}'::json]::varchar[]
              )

I would like to get rid of ::varchar[] typecasting at the end of second array.

CodePudding user response：

The documentation of json_object says:

All values are converted to JSON strings.

So don't use json_object if you want values that are arbitrary JSON values, not strings.

Instead, use json_object_agg:

SELECT json_object_agg(key, value)
FROM UNNEST(array_of_keys, array_of_values) AS kv(key, value)

^{(Online demo with your sample data)}