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?