Skip to content

Instantly share code, notes, and snippets.

@wolph
Created April 6, 2012 10:35
Show Gist options
  • Save wolph/2318757 to your computer and use it in GitHub Desktop.
Save wolph/2318757 to your computer and use it in GitHub Desktop.
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
Copy link

Shouldn't json use double quotes, not single?

@egbutter
Copy link

"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