Re: additional json functionality - Mailing list pgsql-hackers

On Thu, Nov 14, 2013 at 10:54 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
> On 11/14/2013 05:06 PM, Merlin Moncure wrote:
>> On Thu, Nov 14, 2013 at 9:42 AM, Hannu Krosing <hannu@2ndquadrant.com> wrote:
>>> This is supported by the fact that current functions on json-source
>>> treat it as json-object (for example key lookup gives you the value
>>> of latest key and not a list of all matching key values).
>> yeah. hm. that's a good point.
>>
>> Maybe there's a middle ground here: I bet the compatibility issues
>> would be minimized to an acceptable level if the 'xxx_to_json'
>> functions maintained their current behaviors; they would construct the
>> json type in a special internal mode that would behave like the
>> current type does.
>
> Do you have any xxx_to_json usage which can generate a field with
> multiple equal keys ?

Absolutely -- that's what I've been saying all along.  For example:
IIRC the end consumer is jqgrid, although the structure format may be
being done to satisfy some intermediate transformations perhaps in GWT
or in the browser itself.  The point is I didn't define the structure
(I think it sucks too), it was given to me to create and I did.  The
object's dynamic keys and values are moved into json structure by
passing two parallel arrays into a userland function similar to what
Andrew is proposing with json_build functionality.

{   "classDisplayName": null,   "rows": [       {           "PropertyName": "xxx",           "Row": 1,
"Group":"Executive Dashboard",           "MetricName": "Occupancy",           "2012": "95.4%",           "Q2": "96.5%",
         "Q3": "96.3%",           "Q4": "94.8%",           "2013": "95.1%",           "Q2": "94.1%",           "Q3":
"96.0%",          "Q4": "96.1%"       },       {           "PropertyName": "xxx",           "Row": 2,
"Group":"Executive Dashboard",           "MetricName": "Occupancy",           "2012": "95.9%",           "Q2": "97.3%",
         "Q3": "95.7%",           "Q4": "95.2%",           "2013": "93.9%",           "Q2": "93.4%",           "Q3":
"95.3%",          "Q4": "95.1%"       }   ]
 
}

>> but not at cast time.  This preserves compatibility for the important
>> points and allows serialization of structures that are difficult with
>> the binary mode variant.
>
> Seems like this would not play nice with how PostgreSQL type system work
> in general, but could be a way forward if you say that you really do not
> need
> to store the order-preserving, multi-valued json.

Yes, exactly. I'm OK with simplifying the structure for storage
purposes because in that context postgres is the parser and gets to
decide what the precise behaviors are.  Simplifying the stored
structures during upgrade is an OK concession to make, I think.  It is
not safe to assume the structure should be simplified when
serializing.

> But in this case it could also be possible for these function to just
> generate
> json-format "text", and with proper casts this would act exactly as you
> describe
> above, no ?

I think so. if I'm following you correctly.  Maybe you get the best of
both worlds and (mostly) maintaining compatibility by deferring the
decomposition into binary structure in certain contexts.  I'd even
throw in the equality operator (which, thankfully, we haven't defined
yet) as a place where decomposition could happen.  Pretty much any
scenario that isn't involved in raw assembly and output.

merlin