Re: json accessors - Mailing list pgsql-hackers

On Thu, Nov 29, 2012 at 7:58 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> On 11/28/2012 08:16 PM, Hannu Krosing wrote:
>> You could even do a template-less row_from_json which returns a records
>> with all fields converted to
>> the JSON-encodable types and hope that the next conversions will be done
>> by postgreSQL  as needed.
>>
>> insert into tab1 select row_from_json('{"id":100, "ts":"2012-12-21",
>> "data":"End of Everything"}');
>>
>> insert into tab1
>> select * from row_from_json(
>> '[{"id":101, "ts":"2012-12-22", "data":"1st day after End of Everything"}
>>   {"id":102, "ts":"2012-12-22", "data":"2nd day after End of Everything"}
>> ]');
>
> The real problem here is that for any irregularly shaped json it's likely to
> be a bust, and could only possibly work sanely for nested json at all if the
> target type had corresponding array and composite fields.

again, that's pretty a fairly typical case -- crafting json documents
specifically for consumption in postgres.  defining backend types
allows you to skip intermediate iterative marshaling step.

> hstore's
> populate_record works fairly well precisely because hstore is a flat
> structure, unlike json.

agreed. not trying to drag you into the weeds here.  the above is neat
functionality but doesn't cover all the cases so specific accessor
functions in the vein of your proposal are still needed and the hstore
workaround should work pretty well -- sugaring up the syntax for 'all
in wonder' type translations of complicated structures can be done
later if you want to keep things simple in the short term.

so, just hashing out your proposal and making sure it's reasonable
analogous implementation of xpath.  Sleeping on it, I say mostly, but
not quite. how about some changes for json_get:

1) return setof (key, value) in the style of jquery each().
2) we need some way of indicating in the keytext path that we want to
unnest the collecton pointed to by keytext or to just return it.  for
example,  ->* as indicator?
3) use double quotes, and make them optional (as hstore)
4) speaking of hstore, prefer => vs ->?

if you do at least #1 and #2, json_get I think can cover all the bases
for parsing json, meaning you could reproduce the behaviors for each
of your four proposed  just as xpath does for xml.   (you may still
want to add them for posterity or performance though). so no need for
json_each or json_array_unnest etc.

merlin