PLPGSQL parse json IN parameter and compose json OUT parameter

Question

I'm trying to make a stored function on postgres server which could handle json parameters. The server version is 12. It is used as backend server for an android app.

The current process of retrieving data from DB is as follows:

APP ---(http post request with json)---> Nodejs handlers server
Nodejs handlers server (parse App json & validates data)
Nodejs handlers server <---(1 to 5 SQL SELECT/UPDATE/etc queries)---> Postgres server
Nodejs handlers server (composes json response for App)
Nodejs handlers server ---(http response with json)---> APP

The main disadvantage is in multiple queries and processes between Nodejs handler and Postgres server. I decided to optimise it by preparing a complex json by handler which includes user data and actions needed, sending it to Postgres server, which must parse it and make all the job with tables, then compose a json with all needed results. But I'm completely lost in how to parse and compose json inside the stored function on Postgres server.

For example if I have this simple json №1:

{
    "fields": ["name", "age", "rating"],
    "values": ["alex", 24, 7.0],
    "type": ["string", "integer", "real"]
}

And I need to send back json №2 as follows:

{
    "updateDate": 20200101
}

How can I for example get 24 int value from first json and compose the second json inside the function?

CREATE FUNCTION update_user (IN data json, OUT result JSON) AS $$
DECLARE
   age int;
BEGIN
   age := ???
   ...(sql query)...
   result := ???
END $$
LANGUAGE plpgsql;

Thanks for the any help

Pavel Stehule · Accepted Answer · 2020-09-13T08:47:32.113

It needs little bit hard work, because PLpgSQL language doesn't support complex structures well (like more modern or dynamic languages) doesn't do differences between complex structures and json (but for PLpgSQL json type is +/- text type).

If you want to use faster execution, then you need custom type:

CREATE TYPE response_type AS ("fields" text[], "values" text[], "type" text[]);

with this type you can write function:

CREATE OR REPLACE FUNCTION update_user(IN data json)
RETURNS json AS $$
DECLARE                         
  resp response_type;
  age int;
BEGIN
  resp := json_populate_record(resp, data);
  age := resp."values"[array_position(resp."fields", 'age')];
  RAISE NOTICE '%', age;

  RETURN json_build_object('updateDate', CURRENT_DATE);
END;
$$ LANGUAGE plpgsql;

postgres=# select update_user('{
    "fields": ["name", "age", "rating"],
    "values": ["alex", 24, 7.0],
    "type": ["string", "integer", "real"]
}');
NOTICE:  24
┌───────────────────────────────┐
│          update_user          │
╞═══════════════════════════════╡
│ {"updateDate" : "2020-09-13"} │
└───────────────────────────────┘
(1 row)

For PLpgSQL you have to check list of functions for json type - https://www.postgresql.org/docs/current/functions-json.html.

For your purpose can be interesting other PL language https://github.com/plv8/plv8

PLPGSQL parse json IN parameter and compose json OUT parameter

1 Answers1