Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT C.id, C.name, json_agg(E) AS emails FROM contacts C
- LEFT JOIN emails E ON C.id = E.user_id
- GROUP BY C.id;
- id | name | emails
- -----------------------------------------------------------
- 1 | Ryan | [{"id":3,"user_id":1,"email":"hello@world.com"},{"id":4,"user_id":1,"email":"again@awesome.com"}]
- 2 | Nick | [null]
- SELECT C.id, C.name,
- COALESCE(json_agg(E) FILTER (WHERE E.user_id IS NOT NULL), '[]') AS emails
- FROM contacts C
- LEFT JOIN emails E ON C.id = E.user_id
- GROUP BY C.id, C.name
- ORDER BY C.id;
- select
- c.id, c.name,
- case when count(e) = 0 then '[]' else json_agg(e) end as emails
- from contacts as c
- left outer join emails as e on c.id = e.user_id
- group by c.id
- select
- c.id, c.name,
- coalesce(e.emails, '[]') as emails
- from contacts as c
- left outer join (
- select e.user_id, json_agg(e) as emails from emails as e group by e.user_id
- ) as e on e.user_id = c.id
- SELECT C.id, C.name,
- COALESCE(NULLIF(json_agg(E)::TEXT, '[null]'), '[]')::JSON AS emails
- FROM contacts C
- LEFT JOIN emails E ON C.id = E.user_id
- GROUP BY C.id;
- CREATE OR REPLACE FUNCTION public.json_clean_array(p_data JSON)
- RETURNS JSON
- LANGUAGE SQL IMMUTABLE
- AS $$
- -- removes elements that are json null (not sql-null) or empty
- SELECT json_agg(value)
- FROM json_array_elements(p_data)
- WHERE value::text <> 'null' AND value::text <> '""';
- $$;
- CREATE OR REPLACE FUNCTION public.json_clean(p_data JSON)
- RETURNS JSON
- LANGUAGE SQL IMMUTABLE
- AS $$
- -- removes elements that are json null (not sql-null) or empty
- SELECT ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
- FROM json_each(p_data)
- WHERE value::TEXT <> 'null' AND value::TEXT <> '""';
- $$;
- CREATE OR REPLACE FUNCTION public.json_clean(p_data JSON)
- RETURNS JSON
- LANGUAGE SQL IMMUTABLE
- AS $$
- -- removes elements that are json null (not sql-null) or empty
- SELECT json_object_agg(key, value)
- FROM json_each(p_data)
- WHERE value::TEXT <> 'null' AND value::TEXT <> '""';
- $$;
- select
- c.id, c.name,
- array_to_json(array(select email from emails e where e.user_id=c.id))
- from contacts c
- CREATE OR REPLACE FUNCTION public.json_clean_array(data JSON)
- RETURNS JSON
- LANGUAGE SQL
- AS $$
- SELECT
- array_to_json(array_agg(value)) :: JSON
- FROM (
- SELECT
- value
- FROM json_array_elements(data)
- WHERE cast(value AS TEXT) != 'null' AND cast(value AS TEXT) != ''
- ) t;
- $$;
- select
- friend_id as friend,
- json_clean_array(array_to_json(array_agg(comment))) as comments
- from some_entity_that_might_have_comments
- group by friend_id;
- CREATE OR REPLACE FUNCTION public.json_clean(data JSON)
- RETURNS JSON
- LANGUAGE SQL
- AS $$
- SELECT
- ('{' || string_agg(to_json(key) || ':' || value, ',') || '}') :: JSON
- FROM (
- WITH to_clean AS (
- SELECT
- *
- FROM json_each(data)
- )
- SELECT
- *
- FROM json_each(data)
- WHERE cast(value AS TEXT) != 'null' AND cast(value AS TEXT) != ''
- ) t;
- $$;
- SELECT C.id, C.name,
- case when exists (select true from emails where user_id=C.id) then json_agg(E) else '[]' end
- FROM contacts C
- LEFT JOIN emails E ON C.id = E.user_id
- GROUP BY C.id, C.name;
- ...
- array_remove(array_agg(
- jsonb_build_object('att1', column1, 'att2', column2)
- ), to_jsonb('{"att1":null, "att2":null}'::json))
- ...
Add Comment
Please, Sign In to add comment