Обсуждение: 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.klaver@aklaver.com

On 16/11/2021 15:54, David Gauthier wrote:
> 
> Pros and Cons for using JSON ?

If it helps, here's my current use-case. I'm currently working on a 
project wherein data is captured from students for eventual submission 
to the relevant government department. The exact set of data required 
changes every so often, and it's not used anywhere else in the 
application, so I'm going to store it in a JSONB column, with a schema 
stored (also in JSONB) in a separate table which can be used to validate 
the data. I'll have a version number on the schema, and this will be 
kept in the student record also.

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

One of the nice things about Oracle packages is that the code is loaded and global values are set and stored only once
persession. This is very useful for values that are used repeatedly.
 

What is the best way of emulating this behavior in Postgresql?

On 11/16/2021 12:27 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

út 16. 11. 2021 v 18:23 odesílatel DAVID ROTH <adaptron@comcast.net> napsal:

One of the nice things about Oracle packages is that the code is loaded and global values are set and stored only once per session. This is very useful for values that are used repeatedly.

What is the best way of emulating this behavior in Postgresql?

you can use a schemas - you can see it in Orafce

https://github.com/orafce/orafce

Regards

Pavel

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
likethat to change.
 
> 2) The customers for this data is probably going to be python code.  IOW, they can sort things out in their code
afterreading 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

On 11/16/21 12:23, DAVID ROTH wrote:
> One of the nice things about Oracle packages is that the code is loaded and global values are set and stored only
onceper session. This is very useful for values that are used repeatedly.
 
>
> What is the best way of emulating this behavior in Postgresql?
>
>

You can create a temporary table with "ON COMMIT PRESERVE ROWS", which 
is visible in a session and cannot cross the session boundary.  Values 
in the table can be queried throughout the session. That is a pretty 
good approximation of the session variables in Oracle.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com