Set empty array as default in array_agg in postgresql
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 are null. How can I set an empty array as the default for array_agg? This field must be an empty array or an array with data. In some cases, I use the array_length function to filter the data.
source to share
First of all, I wouldn't mix array_agg
with JSON (avoid double quotes, and also use the tag select array( .. subquery ..)
here to get an array, this is somewhat equivalent to yours array_agg(..)
):
test=# select array(select '{"zz": 1}'::jsonb);
array
-----------------
{"{\"zz\": 1}"}
- here you will get ARRAY JSONB and what you really need is one 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 a single value jsonb
(with a JSON array inside) use the function jsonb_agg(..)
.
To replace the NULL
default as usual, you can use the standard function coalesce(..)
:
test=# select coalesce(null::jsonb, '[]'::jsonb);
coalesce
----------
[]
(1 row)
Finally, as I see from additional comments, you need to get the length of your array jsonb
- functions are created for this purpose json_array_length(..)
and jsonb_array_length(..)
see https://www.postgresql.org/docs/current/static/functions-json.html .
source to share