0

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;
VladimirCh
  • 41
  • 5
  • Hi, it doesn't matter, actually. The error is in function call syntax. Anyway... – VladimirCh May 08 '18 at 16:28
  • Yes, I see now, my fault ;) – VladimirCh May 08 '18 at 16:36
  • I wanted to extract an array of json objects from "shops" element – VladimirCh May 08 '18 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 May 08 '18 at 16:48
  • 1
    `shops := array(SELECT json_array_elements(sync_data->'shops'));` PS: Instead of `select into ;` you could to use ` := ;` – Abelisto May 08 '18 at 18:33

1 Answers1

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!

VladimirCh
  • 41
  • 5