Re: How is JSON stored in PG ?

On 11/16/21 07:54, 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.  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.

1) Are you talking json or jsonb?

2) If you are going to store JSON in a non-JSON field then you will need
to cast the field value to json/jsonb to use the JSON capabilities in
Postgres. Not sure that is a good idea.

3) json(b) is stored in a field in a table per
https://www.postgresql.org/docs/current/datatype-json.html:

"The json and jsonb data types accept almost identical sets of values as
input. The major practical difference is one of efficiency. The json
data type stores an exact copy of the input text, which processing
functions must reparse on each execution; while jsonb data is stored in
a decomposed binary format that makes it slightly slower to input due to
added conversion overhead, but significantly faster to process, since no
reparsing is needed. jsonb also supports indexing, which can be a
significant advantage."

>
> Pros and Cons for using JSON ?

Pros

You get the warm fuzzy feeling of using NoSQL in a SQL database

Cons

You get the task of basically unfolding a host of embedded databases.

>
> Thanks for any help !
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com