PATCH: Add hstore_to_json()
in [PgSql]
|
From: "David E. Wheeler" on 3 Jan 2010 13:51 On Jan 3, 2010, at 8:00 AM, Andrew Dunstan wrote: > I think the minimal functionality I'd want is: > > convert record to JSON > convert JSON to record With caveats as to dealing with nested structures (can a record be an attribute of a record?). > extract a value, or set of values, from JSON > composition of JSON There's a lot of functionality in hstore that I'd like to see. It'd make sense to use the same operators for the same operations. I think I'd start with hstore as a basic spec. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 3 Jan 2010 14:40 David E. Wheeler wrote: > On Jan 3, 2010, at 8:00 AM, Andrew Dunstan wrote: > > >> I think the minimal functionality I'd want is: >> >> convert record to JSON >> convert JSON to record >> > > With caveats as to dealing with nested structures (can a record be an attribute of a record?). > We allow composites as fields. The biggest mismatch in the type model is probably w.r.t arrays. JSON arrays can be heterogenous and non-rectangular, AIUI. > >> extract a value, or set of values, from JSON >> composition of JSON >> > > There's a lot of functionality in hstore that I'd like to see. It'd make sense to use the same operators for the same operations. I think I'd start with hstore as a basic spec. > > > OK, but hstores are flat, unlike JSON. We need some way to do the equivalent of xpath along the child axis and without predicate tests. hstore has no real equivalent because it has no nesting. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "David E. Wheeler" on 3 Jan 2010 15:07 On Jan 3, 2010, at 11:40 AM, Andrew Dunstan wrote: > We allow composites as fields. The biggest mismatch in the type model is probably w.r.t arrays. JSON arrays can be heterogenous and non-rectangular, AIUI. Cool, that sounds right. > OK, but hstores are flat, unlike JSON. We need some way to do the equivalent of xpath along the child axis and without predicate tests. hstore has no real equivalent because it has no nesting. You mean so that you can fetch a nested value? Hrm. I agree that it's have to be XPath like. But perhaps we can use a JavaScript-y syntax for it? There could be an operator that returns records: % SELECT '{"foo":{"bar":["a","b","c"]}}' -> '["foo"]'; bar ------------- ("{a,b,c}") % SELECT '{"foo":{"bar":["a","b","c"]}}' -> '["foo"][1]'; 1 ----- (b) And another that returns values where possible and JSON where there are data structures. % SELECT '{"foo":{"bar":["a","b","c"]}}' => '["foo"]'; ?column? ------------------ {"bar":{a,b,c}"} % SELECT '{"foo":{"bar":["a","b","c"]}}' => '["foo"][1]'; ?column? ---------- b Not sure if the same function can return different values, or if it's even appropriate. In addition to returning JSON and TEXT as above, we'd also need to be able to return numbers: % SELECT '{"foo":{"bar":[22,42]}}' => '["foo"][1]'; ?column? ---------- 42 Thoughts? David -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Hitoshi Harada on 3 Jan 2010 19:18 2010/1/4 David E. Wheeler <david(a)kineticode.com>: > On Jan 3, 2010, at 11:40 AM, Andrew Dunstan wrote: > >> We allow composites as fields. The biggest mismatch in the type model is probably w.r.t arrays. JSON arrays can be heterogenous and non-rectangular, AIUI. > > Cool, that sounds right. Does it mean you should create composite type to create anonymous JSON? >> OK, but hstores are flat, unlike JSON. We need some way to do the equivalent of xpath along the child axis and without predicate tests. hstore has no real equivalent because it has no nesting. > > You mean so that you can fetch a nested value? Hrm. I agree that it's have to be XPath like. But perhaps we can use a JavaScript-y syntax for it? There could be an operator that returns records: > > % SELECT '{"foo":{"bar":["a","b","c"]}}' -> '["foo"]'; > bar > ------------- > ("{a,b,c}") > > % SELECT '{"foo":{"bar":["a","b","c"]}}' -> '["foo"][1]'; > 1 > ----- > (b) That sounds good and seems possible, as far as operator returns JSON always. Perhaps every JSON fetching returns JSON even if the result would be a number. You can cast it. % SELECT ('{"foo":{"bar":["a","b","c"]}}' -> '["foo"][1]')::text; 1 ----- b Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 3 Jan 2010 19:30
Hitoshi Harada wrote: > 2010/1/4 David E. Wheeler <david(a)kineticode.com>: > >> On Jan 3, 2010, at 11:40 AM, Andrew Dunstan wrote: >> >> >>> We allow composites as fields. The biggest mismatch in the type model is probably w.r.t arrays. JSON arrays can be heterogenous and non-rectangular, AIUI. >>> >> Cool, that sounds right. >> > > Does it mean you should create composite type to create anonymous JSON? > > No, not in the least. We should still store JSON as text. We should simply be able to convert a JSON value to a record of an existing type (providing it has the right shape) and a record (of any shape) to JSON. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |