Created

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

Some functions to convert arrays/hstore to json :)

View to_json.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!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.