Обсуждение: JSONB filed with default JSON from a file

All,

I'm new to JSONB datatype. We would like to store a nested JSON file in this field. Since the JSON is nested, we wanted to create JSON with default value from an external JSON file.

My address table looks like,

CREATE TABLE address (

id CHAR(36) UNIQUE NOT NULL,

address JSONB NOT NULL

);

For example, the default JSON will look like,

$ cat address_default.json

{

  "address": {

    "address1": "175 N Street",

    "address2": "Timabktu",

    "location": [

      {

        "city": "Utopia",

        "geolocation": [

          {

            "lat": "12.345",

            "long": "12.1234"

          }

        ],

        "state": "Nowhere"

      }

    ],

    "zip": "96001"

  }

}

How do I make the address_default.json as the default JSON value for the address column?

On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru <mrcasablr@gmail.com> wrote:
>>
>> I assume that you could declare the column as
>>
>> address jsonb not null default 'your json here'::jsonb;
>
>
> Thanks! However, this involves writing the entire JSON in the schema file looks inconvenient. I was hoping I would be
ableto reference to an external JSON file which could be used for the default value.
 

1) Stick the default json in a table somewhere, say default_json with
one row, one column
2) Wrap the table with a function, default_json() that returns the
value from the table
3) Make a default function for the table, DEFAULT default_json().
That way you externalize the default into the database

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru <mrcasablr@gmail.com> wrote:
>> Thanks! However, this involves writing the entire JSON in the schema file looks inconvenient. I was hoping I would
beable to reference to an external JSON file which could be used for the default value. 

> [ put it in a table instead ]

Yeah.  If you really insist on having it in a file outside the database,
you can, but you'll need a superuser-privileged function to read it
from that file.  Aside from the security aspects, this sort of thing
is an antipattern because it opens you up to backup/restore problems
("oh, we needed that file too?"), replication problems, yadda yadda.
And what are you buying by doing it like that?  Better to keep it inside
the DB instead.

            regards, tom lane

On 08/13/2018 12:11 PM, Tom Lane wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> On Mon, Aug 13, 2018 at 12:56 PM mrcasa bengaluru <mrcasablr@gmail.com> wrote:
>>> Thanks! However, this involves writing the entire JSON in the schema file looks inconvenient. I was hoping I would
beable to reference to an external JSON file which could be used for the default value.
 
>
I'm struggling with the notion of default address.  Is the point to get 
a "blank" json structure in place.  Perhaps to fill in later? Otherwise, 
it seems like saying the default name is "Bob"? Rarely the value wanted.