Array of objects in postgresql

Question

I'm trying to create a function that receives a json and that json contains an array of objects. However postgresql marks an error when I save the array in a variable.

create function test_create_table(_data json) returns void
    language plpgsql
as
$$
declare
    _schema     text;
    _catalog    text;
    _table_name text;
    _fields     jsonb[];

begin

    _table_name := (_data ->> 'tableName')::text;
    _schema := (_data ->> 'schema')::text;
    _catalog := (_data ->> 'catalog')::text;
    _fields := (_data ->> 'fields'):: jsonb[];

    raise notice 'fields %',_fields;

end
$$;

This is the error I get:

[2020-08-10 09:03:22] [22P02] ERROR: malformed array literal: "[
[2020-08-10 09:03:22] {
[2020-08-10 09:03:22] "name": "field",
[2020-08-10 09:03:22] "type": "integer"
[2020-08-10 09:03:22] },
[2020-08-10 09:03:22] {
[2020-08-10 09:03:22] "name": "field2",
[2020-08-10 09:03:22] "type": "text"
[2020-08-10 09:03:22] },
[2020-08-10 09:03:22] {
[2020-08-10 09:03:22] "name": "field3",
[2020-08-10 09:03:22] "type": "json"
[2020-08-10 09:03:22] },
[2020-08-10 09:03:22] {
[2020-08-10 09:03:22] "name": "field4",
[2020-08-10 09:03:22] "type": "varchar"
[2020-08-10 09:03:22] }
[2020-08-10 09:03:22] ]"
[2020-08-10 09:03:22] Detail: "[" must introduce explicitly-specified array dimensions.
[2020-08-10 09:03:22] Where: PL/pgSQL function test_create_table(json) line 13 at assignment

This is what I am sending to the function:

select *
from public.test_create_table(
        '{
          "tableName": "test1",
          "schema": "my_schema",
          "catalog": "my_catalog",
          "fields": [
            {
              "name": "field",
              "type": "integer"
            },
            {
              "name": "field2",
              "type": "text"
            },
            {
              "name": "field3",
              "type": "json"
            },
            {
              "name": "field4",
              "type": "varchar"
            }
          ]
        }'
    );

I have been looking for possible solutions but it was not very helpful. At this moment I am not sure if postgresql supports an array of objects.

You are mixing datatype arrays with json arrays. Please change the datatype of _fields to jsonb and see if that works. — Mike Organek
– Mike Organek, Commented Aug 10, 2020 at 14:16

Laurenz Albe · Accepted Answer · 2020-08-10 14:58:29Z

1

Declare _fields as jsonb, not as jsonb[].

To iterate through the elements of a jsonb array, use code like

FOR j IN
   SELECT x FROM jsonb_array_elements(_fields) AS x(x)
LOOP
   ...
END LOOP;

answered Aug 10, 2020 at 14:58

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Array of objects 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