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
