What I Wish Someone Told Me About Postgres

Re:What I Wish Someone Told Me About Postgres by nodakai on Tuesday November 12, 2024 @08:29AM (#42115313)

That nested JSON query operator chains such as json_col->'foo'->'bar'->>'baz' internally return (copy) entire sub-objects at each level and can be much slower than jsonb_path_query(json_col, '$.foo.bar.baz') for large JSONB data

... although I haven't had the chance to verify this myself

[ Parent | on HN ]

Re:What I Wish Someone Told Me About Postgres by mdaniel on Tuesday November 12, 2024 @12:04PM (#42117296)

I got nerd-sniped on this, because I actually hadn't heard that before and would be horrified if it were true. It took some non-trivial digging to even get down into the "well, what does foo->>'bar' even map to in C?" level. I for sure am not claiming certainty, but based merely on "getIthJsonbValueFromContainer" <https://sourcegraph.com/github.com/postgres/postgres@REL_17_...> it does seem that they do palloc copies for at least some of the JSONB calls

[ Parent | on HN ]

Re:What I Wish Someone Told Me About Postgres by anarazel on Tuesday November 12, 2024 @12:21PM (#42117503)

Postgres does have infrastructure to avoid this in cases where the result is reused, and that's used in other places, e.g. array constructors / accessors. But not for jsonb at the moment.

[ Parent | on HN ]

Re:What I Wish Someone Told Me About Postgres by hans_castorp on Tuesday November 12, 2024 @08:39AM (#42115376)

You can also use #>> operator for that:

    json_col #>> '{foo,bar,baz}'

[ Parent | on HN ]