CODEWITHSUNDEEP
arraysjsonpostgresql
Edisson Andres Garcia
Edisson Andres Garcia

Reputation: 23

Array of objects in postgresql

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.

Upvotes: 0

Views: 256

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247865

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;

Upvotes: 1

Related Questions