postgresql function CAST json ARRAY to A TYPE

Question

I try to cast a JSON to a TYPE VOID_EVENT_CREATE, but I didn't work because of the array. I'm going to use the query with NodeJS that why I would like to cast the JSON to a special type. It's easier after.

CREATE TYPE VOID_EVENT_CREATE2 AS
(
 -- name type
 --------- --------
 car_id INTEGER
 , time_deviation TIMESTAMP
 , time_start_avg TIMESTAMP
 , subscribable BOOLEAN
 , roundtrip BOOLEAN
 , recurrent BOOLEAN
 , gps_points API_GPS_POINT []
 , hours_type_go API_ENUMERATOR
 , hours_type_return API_ENUMERATOR
 , pref_state API_ENUMERATOR
 , date_hour_go TIMESTAMP
 , date_hour_return TIMESTAMP
);

My function :

CREATE OR REPLACE FUNCTION test_eventsCreatePUT
 (
 void_event VOID_EVENT_CREATE2
 , apikey TEXT
 )
 RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
 bool BOOLEAN;
 me UUID;
 ann ANNOUNCEMENT;
 hours_type_go API_ENUMERATOR;
 hours_type_return API_ENUMERATOR;
BEGIN
 RAISE NOTICE 'IN v01_api_put_eventsCreatePUT';
 SELECT *
 FROM cheickApiKey
 (
 apikey
 )
 INTO me;
 -- PREPARE ANNOUNCEMENT
 ann.car_id := void_event.car_id;
 ann.ann_time_deviation := void_event.time_deviation;
 ann.ann_time_start_avg := void_event.time_start_avg;
 ann.ann_subscribable := void_event.subscribable;
 ann.ann_recurrent := void_event.recurrent;
 -- ONE WAY ----------------------
 hours_type_go := void_event.hours_type_go;
 ann.hours_type_id := hours_type_go.id;
 FOR i IN 1..array_length(void_event.days, 1)
 LOOP
 -- CALCUL EXACT DAY
 SELECT *
 FROM
 event_find_next_dayofweek
 (
 void_event.date_hour_go
 , void_event.days [i]
 )
 INTO
 ann.ann_dh;
 -- CREATE ONE EVENT
 SELECT *
 FROM event_create
 (
 me
 , void_event.gps_points
 , ann
 , pref_state
 )
 INTO bool;
 END LOOP...........

How I call it :

SELECT *
FROM test_eventsCreatePUT
(
 json_populate_record(NULL :: VOID_EVENT_CREATE2,
 '{
 "car_ID": 1,
 "time_deviation": "2017-08-01T14:8:24.019Z",
 "time_start_avg": "2017-08-01T14:18:24.019Z",
 "subscribable": true,
 "roundtrip": true,
 "recurrent": true,
 "gps_points": [
 {
 "gps_id": 300,
 "gps_street": "24 grand rue",
 "gps_city": "illkirch",
 "gps_postalcode": "67120",
 "gps_type": "(387,GPS_PERSO)",
 "gps_lat": -51.32541,
 "gps_lon": 42.80386,
 "gps_counter": 0,
 "gps_label": "alcatel"
 },
 {
 "gps_id": 300,
 "gps_street": "24 grand rue",
 "gps_city": "illkirch",
 "gps_postalcode": "67120",
 "gps_type": "(387,GPS_PERSO)",
 "gps_lat": -51.32541,
 "gps_lon": 42.80386,
 "gps_counter": 0,
 "gps_label": "alcatel"
 }
 ],
 "hours_type_go": {
 "ID": 500,
 "label": "DEPART_TIME"
 },
 "hours_type_return": {
 "ID": 500,
 "label": "DEPART_TIME"
 },
 "Pref_state": {
 "ID": 20,
 "label": "PREF_DRIVER_OWNER"
 },
 "date_hour_go": "2017-08-01T14:10:24.019Z",
 "date_hour_return": "2017-08-01T14:10:24.019Z"
 }'),
 'HMX72DNI5DORZB63QQM0SGKWV74VRB2VMXHM85KGF9KP'
);

And I receive an error :

[22P02] ERROR: malformed array literal: "[
 {
 "gps_id": 300,
 "gps_street": "24 grand rue",
 "gps_city": "illkirch",
 "gps_postalcode": "67120",
 "gps_type": "(387,GPS_PERSO)",
 "gps_lat": -51.32541,
 "gps_lon": 42.80386,
 "gps_counter": 0,
 "gps_label": "alcatel"
 },
 {
 "gps_id": 300,
 "gps_street": "24 grand rue",
 "gps_city": "illkirch",
 "gps_postalcode": "67120",
 "gps_type": "(387,GPS_PERSO)",
 "gps_lat": -51.32541,
 "gps_lon": 42.80386,
 "gps_counter": 0,
 "gps_label": "alcatel"
 }

After checking a lot google, I saw that maybe json_populate_record cannot cast a JSON array to an object? I don't know how continue. This is the best for me if I can send a big JSON and CAST it to a type.

Thank for your help

Answer 1

You keep asking the same question. You're missing it.

This is a JSONB array. A JSONB array of objects.

SELECT '[{"a":1}, {"a":2}]'::JSONB;

A function can take that, it's just one JSONB type. This represents your array of objects.

You can make one function that process that whole thing. It'll work fine!

SELECT x, pg_typeof(x), jsonb_typeof(x)
FROM (VALUES ( '[{"a":1}, {"a":2}]'::JSONB ))
 AS t(x);
 x | pg_typeof | jsonb_typeof 
----------------------+-----------+--------------
 [{"a": 1}, {"a": 2}] | jsonb | array

Here the function pg_typeof and jsonb_typeof get the whole jsonb, array. Or if you don't need all of that stuff, you can make a function that just gets the literals that you would call twice. Once with {"a":1}. Once with {"a":2}

SELECT y, pg_typeof(y), jsonb_typeof(y)
FROM (VALUES ( '[{"a":1}, {"a":2}]'::JSONB ))
 AS t(x)
CROSS JOIN LATERAL jsonb_array_elements(x)
 AS y;
 y | pg_typeof | jsonb_typeof 
----------+-----------+--------------
 {"a": 1} | jsonb | object
 {"a": 2} | jsonb | object

Now you have two calls. A function that processes this also takes JSONB, but the function won't have to loop through a json array.

Or you can make one function that processes the unpacked data inside it which makes for a much more simple function.

CREATE TYPE mytype AS ( a int );
SELECT a, pg_typeof(a)
FROM (VALUES ( '[{"a":1}, {"a":2}]'::JSONB ))
 AS t(x)
CROSS JOIN LATERAL jsonb_populate_recordset(null::mytype, x);
 a | pg_typeof 
---+-----------
 1 | integer
 2 | integer
(2 rows)

Now the function processing the above just has to process an interger! That makes it often the most simple, and reusable.

So what do you want to do,

1. Process an entire JSON array.
2. Process the JSON objects inside (you only need access to one object at a time)
3. Process just the individual key/values in the object?