Re: PATCH: Add hstore_to_json()

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