query_to_json.sql

gistfile1.txt

create or replace function xml2json (xml) returns text
language plperlu as
$func$

use XML::XML2JSON;

use strict;

my $xml = shift;
my $XML2JSON = XML::XML2JSON->new(pretty => 0); # 1 for readable json

# work around horrid XML2JSON bug with elements consisting for <foo>0/foo>
$xml =~ s!<([^>]+)>0</\1>!<$1>9999999999999999</$1>!g;
# work around another horrid bug, which turns a string of all blanks into {}
$xml =~ s!<([^>]+)>(\s*)</\1>!<$1>$2,Mxy;zptlk~</$1>!g;

my $json = $XML2JSON->convert($xml);

$json =~ s/9999999999999999/0/g;
$json =~ s/,Mxy;zptlk~//g;

# flatten the structure

# nulls:
$json =~ s/\{[^"]*"\@xsi:nil" ?: ?"true"[^"}]*}(,?)/null$1/gs;
# numbers (not in scientific notation):
# don't dequote a number with a leading zero if it's followed by
# another digit - e.g. a zip code
$json =~ s/{[^"]*"\$t" ?: ?"(-?(0|([1-9]\d*))(\.\d+)?)"[^"}]*}(,?)/$1$5/gs;
# booleans:
$json =~ s/{[^"]*"\$t" ?: ?"(true|false)"[^"}]*}(,?)/$1$2/gs;
# strings:
# arrays and composites are not decomposed, they just come in
# as strings
$json =~ s/{[^"]*"\$t" ?: ?"(([^"]|\\")*)"[^"}]*}(,?)/"$1"$3/gs;

#remove wrapper 
# single row - make it an array of one
$json =~ s/^.*?"row"\s*:\s*\{/\[\{/;
$json =~ s/\}\s*}\s*}\s*$/\}\]/;
# array of rows
$json =~ s/^.*?"row"\s*:\s*\[/\[/;
$json =~ s/\]\s*}\s*}\s*$/\]/;


return $json;

$func$;

create or replace function query_to_json(query text) returns text
language sql as
$func$
  select xml2json(query_to_xml($1,true,false,''))
$func$;