Re: json (b) and null fields

On 09/29/2014 03:23 PM, Pavel Stehule wrote:
>
>
> 2014-09-27 14:00 GMT+02:00 Stephen Frost <sfrost(at)snowman(dot)net
> <mailto:sfrost(at)snowman(dot)net>>:
>
> Andrew, all,
>
> * Andrew Dunstan (andrew(at)dunslane(dot)net
> <mailto:andrew(at)dunslane(dot)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.
>
>
> It is better than nothing, but it is not nice for JSON due 2x parsing.
> Probably it is not issue for jsonb.
>
> It is not nice, but I have not better .. and it will be faster than
> any custom solution in plpgsql.
>
> Personally, I am not sure, maybe is better to fix row_to_json
>
>

No. There are several reasons for not doing this, starting with the ones
in my original email on the topic and Tom's objection to the use of
multiple default options.

This is a non-starter.

JSON parsing is actually pretty darn fast. Every json (as opposed to
jsonb) function reparses the json. It's true that this is not nearly as
fast as processing jsonb, but I think for this purpose it's probably not
too bad.

Frankly, row_to_json is not the most useful case where we could do this
anyway, so I don't think we should be looking for a heroic effort there.
I think the consensus for separate functions is the way to go.

I have made a start on coding strip_nulls functions. I'll post a patch
before too long.

cheers

andrew