Postgresql terminal throws illegal array element error when trying to type JSON

user_13908462 0
Viewed 88

I am trying to create a recursive JSON equality operator. Right now the trouble is that for some reason when I type out a JSON literal in a PL-PGSQL anonymous block for testing, it constantly throws an illegal array element syntax error.

Below is my current code.

DO $$
    DECLARE
        keys1 text[];
        keys2 text[];
        json1 json;
    BEGIN
        json1 := '{"a5":16,"b2":14}'::json;
        SELECT * FROM json_object_keys(json1) into keys1;
    END
$$;
postgresql stored-procedures arrays sql json
1 Answers

The problem is the into clause of the select statement. json_object_keys() returns a set of text, that is a resultset made of 1 column and 0 to N rows. You cannot assign it to a variable declared as an array of text

One workaround to make the assignment possible without changing the type of the target variable is to use array_agg():

select array_agg(k) into keys1 from json_object_keys(json1) as x(k);
user_106767160
Related