Re: How is JSON stored in PG ?

On Tue, 2021-11-16 at 10:54 -0500, David Gauthier wrote:
> Hi:
>
> psql (11.5, server 11.3) on linux
>
> I'm considering using JSON as a datatype for something I'm working on.  The reasons are...
>
> 1) the 'metadata' (if you want to call it that) in JSON is very flexible.  Doesn't require an alter table or anything like that to change.
> 2) The customers for this data is probably going to be python code.  IOW, they can sort things out in their code after reading the whole JSON file/record.
> 3) Nice array of built-in functions for this datatype.
> 4) There does appear to be the ability to formulate query predicates on the JSON content (just in case)
>
> But #4 has me a bit worried.  I'm wondering how PG stores this data given that its content can
> be specified in a query predicate.

I don't follow. There are JSON functions and operators you can use, and then there is the
very powerful JSONPATH query language.

> Does PG just store the content in traditional PG tables ?  If so, I can do that myself.
> If not, is there a penalty to be paid at query time if PG needs to get the JSON data, then
> dismantle into a temp table (or something like that) to query.

Yes, it is stored in tables. But "jsonb" is stored in a binary data structure that makes
it fast and efficient to access attributes and values.

I have written up the indications and counter-indications for using JSON here:
https://www.cybertec-postgresql.com/en/json-postgresql-how-to-use-it-right/

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com