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 per session. This is very useful for values that are used repeatedly.

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

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

I can see how schemas can be used to keep related tables and other object together. I may be missing something but I don't see how they can be used to emulate the "once-per-session" behavior of Oracle packages. Have I missed something?

Hi

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

I can see how schemas can be used to keep related tables and other object
together. I may be missing something but I don't see how they can be used
to emulate the "once-per-session" behavior of Oracle packages. Have I
missed something?

There is nothing similar in Postgres.

I am working session variables, that should be created in schema too. As
workaround you can use GUC - configuration variables, that can be used for
storing private values too.

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 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

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 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 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