Create nested json in postgresql

Question

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.

Because ARRAY is a Postgres array not a JSON array. See JSON section 8.14.1. JSON Input and Output Syntax and JSON functions Table 9.46. JSON Creation Functions. Be aware that there are two JSON types, json and jsonb. — Adrian Klaver
– Adrian Klaver, Commented Mar 10, 2022 at 16:44
there is no JSON arrays at all. There are two Postgres arrays of keys and values. difference in JSON and JSONB is not significant here — Andrey Kapliev
– Andrey Kapliev, Commented Mar 10, 2022 at 16:47
Yeah, I was not playing close enough attention. So: 1) What are you trying to achieve? An example of the input data and what the desired output is would be helpful. I'm guessing there is a better way to get to the result you want. 2) Your title says jsonb the code is for json_object which produces json type. — Adrian Klaver
– Adrian Klaver, Commented Mar 10, 2022 at 16:54
I'm trying to put json or jsonb object as a value for given set of keys. For that purpose there is no difference in the behavior of json or jsonb objects — Andrey Kapliev
– Andrey Kapliev, Commented Mar 10, 2022 at 17:20
Understood, I'm just suggesting that make the title match the code to reduce confusion on what you are trying to do. To get an answer it would help to have the sample data. Right now ` ARRAY[set_of_values::json]::varchar[],` is taking a Postgres ARRAY of json values and turning it into a Postgres varchar ARRAY which is why you are getting the escaped stringification. — Adrian Klaver
– Adrian Klaver, Commented Mar 10, 2022 at 17:31

Bergi · Accepted Answer · 2022-03-10 17:57:13Z

2

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)}

edited Mar 10, 2022 at 17:57

answered Mar 10, 2022 at 17:46

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create nested json in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related