public
Last active

Some functions to convert arrays/hstore to json :)

  • Download Gist
to_json.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
CREATE OR REPLACE FUNCTION escape_json (text) RETURNS text AS $$
SELECT replace($1, '''', '\'''); $$ LANGUAGE SQL IMMUTABLE;
 
 
CREATE OR REPLACE FUNCTION to_json(text) RETURNS text AS $$
SELECT escape_json($1) $$ LANGUAGE SQL IMMUTABLE;
 
 
CREATE OR REPLACE FUNCTION to_json(KEY text, value text) RETURNS text AS $$
SELECT '''' || to_json($1) || ''': ''' || to_json($2) || ''''; $$ LANGUAGE SQL IMMUTABLE;
 
 
CREATE OR REPLACE FUNCTION to_json(hstore) RETURNS text AS $$
SELECT '{' || array_to_string(array_agg(to_json(item.KEY, item.value)), ', ') || '}'
FROM each($1) item; $$ LANGUAGE SQL IMMUTABLE;
 
 
CREATE OR REPLACE FUNCTION to_json(text[]) RETURNS text AS $$
SELECT to_json(hstore($1)); $$ LANGUAGE SQL IMMUTABLE;
 
SELECT to_json(hstore(array[array['a', 'b'], array['c', 'd']])) two_dimensional_array_to_hstore_to_json,
to_json(array[array['a', 'b'], array['c', 'd']]) two_dimensional_array_to_to_json,
to_json(hstore(array['a', 'b'], array['c', 'd'])) multi_array_to_hstore_to_json,
to_json(hstore(array['a', 'b', 'c', 'd'])) array_to_hstore_to_json,
to_json(array['a', 'b', 'c', 'd']) array_to_to_json;

Shouldn't json use double quotes, not single?

"A value can be a string in double quotes"
via json standard

Thank you for this great example, though!

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.