Passing json containing array of objects to PostgreSQL function throws malformed array literal. Unexpected array element

Question

I have a simple PostgreSQL function I want to pass complex JSON to:

CREATE OR REPLACE FUNCTION foo(sync_data json)
   RETURNS json AS
$body$
DECLARE
...
END;
$body$  LANGUAGE plpgsql;

Calling it with something like this:

SELECT foo('{"deviceId": 1, "shops": [{"id": 1}, {"id": 2}]}'::json);

gives me the following error:

ERROR: malformed array literal: "{"id": 1}"
DETAIL: Unexpected array element.
CONTEXT: PL/pgSQL function foo(json) line 8 at SQL statement
SQL state: 22P02

I need to pass complex JSON as a parameter, containing arrays of objects.

Thanks!

PS: Here is the full function text:

CREATE OR REPLACE FUNCTION foo(sync_data json)
   RETURNS json AS
$body$
DECLARE
    res_json json;
    device_id integer;
    shops json ARRAY;
BEGIN
    SELECT json_extract_path(sync_data, 'deviceId') INTO device_id;
    SELECT json_array_elements(json_extract_path(sync_data, 'shops')) INTO shops;
    SELECT json_build_object('devId', device_id) INTO res_json;
    RETURN res_json;
END;
$body$  LANGUAGE plpgsql;

Hi, it doesn't matter, actually. The error is in function call syntax. Anyway... — VladimirCh
– VladimirCh, Commented May 8, 2018 at 16:28
I wanted to extract an array of json objects from "shops" element — VladimirCh
– VladimirCh, Commented May 8, 2018 at 16:38
json_array_elements returns a set of json objects. So a json array is the wrong type to hold this data. You could change it to just json, but then you will only get the first result from json_array_elements. — 404
– 404, Commented May 8, 2018 at 16:48
shops := array(SELECT json_array_elements(sync_data->'shops')); PS: Instead of select <expression> into <variable>; you could to use <variable> := <expression>; — Abelisto
– Abelisto, Commented May 8, 2018 at 18:33

VladimirCh · Accepted Answer · 2018-05-09 12:34:42Z

3

The problem was with the syntax inside the function. The correct syntax for parsing array of objects and assigning it to json ARRAY:

CREATE OR REPLACE FUNCTION foo(sync_data json)
   RETURNS json AS
$body$
DECLARE
    res_json json;
    device_id integer;
    shops json ARRAY;
BEGIN
    SELECT json_extract_path(sync_data, 'deviceId') INTO device_id;
    shops := ARRAY(SELECT json_array_elements(sync_data->'shops'));
    SELECT json_build_object('Dev_id', device_id, 'shop1', shops[1], 'shop2', shops[2]) INTO res_json;
    RETURN res_json;
END;
$body$  LANGUAGE plpgsql;

Thanks to eurotrash and Abelisto!

answered May 9, 2018 at 12:34

VladimirCh

411 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

Passing json containing array of objects to PostgreSQL function throws malformed array literal. Unexpected array element

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related