Skip to content

@WoLpH /to_json.sql
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Some functions to convert arrays/hstore to json :)
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;
@joevandyk

Shouldn't json use double quotes, not single?

@egbutter

"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.