Re: json (b) and null fields - Mailing list pgsql-hackers

From Andrew Dunstan
Subject Re: json (b) and null fields
Date
Msg-id 5426FA87.2040407@dunslane.net
Whole thread Raw
In response to Re: json (b) and null fields  (Stephen Frost <sfrost@snowman.net>)
Responses Re: json (b) and null fields
List pgsql-hackers
On 09/27/2014 08:00 AM, Stephen Frost wrote:
> Andrew, all,
>
> * Andrew Dunstan (andrew@dunslane.net) wrote:
>> I should have been paying a bit more attention to the recent work on
>> adding an ignore_nulls option to row_to_json(). Here are some
>> belated thought. I apologize to Pavel and Stephen for not having
>> commented earlier.
> No problem at all and thanks for continuing to think about it!  We
> certainly still have quite a bit of time til 9.5 to get this right.
>
>> I think this is really a bandaid, and it will fail to catch lots of
>> cases. Several examples:
> As discussed on IRC- I agree.  I tend to think of JSON objects as
> relatively simple hstore-like structures and so hadn't considered the
> complex structure case (as I'm guessing Pavel hadn't either).
>
>> I think a much more comprehensive solution would be preferable. What
>> I have in mind is something like
>>
>>      json_strip_null_fields(json) -> json
>>
>> and a similar function for jsonb.
> Right, this makes sense to me.
>
>> These would operate recursively. There is a downside, in that they
>> would be required to reprocess the json/jsonb. But adding an option
>> like this to all the json generator functions would be seriously
>> ugly, especially since they are mostly aggregate functions or
>> variadic functions. At least in the jsonb case the cost of
>> reprocessing is likely to be fairly low.
> Yeah, I don't see adding this option to all json generator functions as
> making a lot of sense but rather just to the select few things which it
> really makes sense for and then having a function which can be used by
> users to do the same for results from other operations.
>
>     


I guess I'm questioning the wisdom of keeping it for row_to_json given 
that it doesn't operate recursively anyway (and making it do so would be 
difficult and ugly).

The counter argument for this is that nested composites and arrays of 
composites are relatively rare in records, so providing a fast 
non-recursive stripping of nulls for the common case is reasonable.

If we're going to keep this, I think we also need to provide it 
(non-recursive) for json_agg via an optional second argument. This 
should be a fairly simple change: just steer the result via 
composite_to_json if it's a record, rather than to datum_to_json.

cheers

andrew



pgsql-hackers by date:

Previous
From: Dean Rasheed
Date:
Subject: Re: WITH CHECK and Column-Level Privileges
Next
From: Andres Freund
Date:
Subject: Re: Escaping from blocked send() reprised.