Example of a PostgreSQL function to convert json string literal to a table.

Was not able to google one up readily, so created this sample:

	create or replace function fn_events_util_get_changes_table_from_json (
	p_changes_json text
	)
	/* Usage:
	select * from fn_events_util_get_changes_table_from_json(
	'['
	'{"uuid":"389f893f-45a5-492b-b2db-ccef4720d5a3", "name": "name1", "count_added":1, "count_updated":2, "count_deleted":3},'
	'{"uuid":"389f893f-45a5-492b-b2db-ccef4720d5a6", "name": "name2", "count_added":0, "count_updated":0, "count_deleted":1}'
	']'
	)
	*/
	returns table (
	id uuid,
	"name" text,
	count_added int,
	count_updated int,
	count_deleted int
	)
	language plpgsql
	as $$
	begin
	return query
	select
	(change_json->>'uuid')::uuid as uuid,
	(change_json->>'name')::text as "name",
	(change_json->>'count_added')::int as "count_added",
	(change_json->>'count_updated')::int as "count_updated",
	(change_json->>'count_deleted')::int as "count_deleted"
	from
	(select ids_json_array::json as change_json from json_array_elements_text(p_changes_json::json) as ids_json_array) as ids_json;
	end;$$

view raw convert-json-string-literal-to-table-postgresql.sql hosted with ❤ by GitHub

Hope it might help someone!