Set empty array as default value in array_agg in postgresql

Question

I have a view:

CREATE OR REPLACE VIEW microservice_view AS
  SELECT
    m.id :: BIGINT,
    m.name,
    m.sending_message_rate :: BIGINT,
    m.max_message_size :: BIGINT,
    m.prefetch_count :: BIGINT,
    (SELECT COALESCE(json_agg(DISTINCT node_id), '[]')
     FROM public.microservice_node
     WHERE microservice_id = m.id) AS nodes,

    (SELECT array_agg(DISTINCT json_build_object('id', transport_id :: INT,
                                                 'is_available', (credentials ->> 'is_available') :: BOOLEAN,
                                                 'username', credentials ->> 'username',
                                                 'password', credentials ->> 'password',
                                                 'default', (default_transport) :: BOOLEAN) :: JSONB
    )
     FROM transport_microservice
     WHERE microservice_id = m.id) AS transports
  FROM public.microservice m
  GROUP BY m.id
  ORDER BY m.id ASC;

Sometimes transports is null. How can I set an empty array as a default value for array_agg? This field should be an empty array or array with data. In some cases I use array_length function to filter the data.

Coalesce it with something, just like you did with json_agg() — Nick Barnes
– Nick Barnes, Commented Jul 7, 2017 at 7:02
@NickBarnes json_agg is returning json and I can't using array_length — Illorian
– Illorian, Commented Jul 7, 2017 at 7:07
@NickBarnes also when I tried used coalesce I get {} instead of [] — Illorian
– Illorian, Commented Jul 7, 2017 at 7:12
@Illoran, but {} is a actually empty array. Try select array[]::text[]; — Tom-db
– Tom-db, Commented Jul 7, 2017 at 7:35

Nick · Accepted Answer · 2017-07-07 14:34:00Z

3

First of all, I wouldn't mix array_agg with JSON (notice double quotes escaping; also I use select array( .. subquery ..) trick here to get an array, it's at some extent an equivalent to your array_agg(..)):

test=# select array(select '{"zz": 1}'::jsonb);
      array
-----------------
 {"{\"zz\": 1}"}

-- here you'll get ARRAY of JSONB, while what you really need is single JSONB value with an embedded array inside:

test=# select pg_typeof(array(select '{"zz": 1}'::jsonb));
 pg_typeof
-----------
 jsonb[]
(1 row)


test=# select pg_typeof('[{"zz": 1}]'::jsonb);
 pg_typeof
-----------
 jsonb
(1 row)

To get single jsonb value (with JSON array inside), use jsonb_agg(..) function.

To substitute NULL value by some default, as usual, you can use standard function coalesce(..):

test=# select coalesce(null::jsonb, '[]'::jsonb);
 coalesce
----------
 []
(1 row)

Finally, as I see from additional comments, you need to get array length of your jsonb -- there are functions json_array_length(..) and jsonb_array_length(..) designed for this purpose, see https://www.postgresql.org/docs/current/static/functions-json.html.

edited Jul 7, 2017 at 14:34

answered Jul 7, 2017 at 7:47

Nick

2,54315 silver badges21 bronze badges

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

2 Comments

Illorian Over a year ago

Thanks. It's really helpfully for me. P.S. In PostgreSQL 9.5.7 documentation I found jsonb_agg

Nick Over a year ago

@Illorian you're welcome. Right, jsonb_agg exists, my memory failed here a bit -- there is no row_to_jsonb but there is row_to_json function, but this is another story.

Collectives™ on Stack Overflow

Set empty array as default value in array_agg in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related