Thread: 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?
Hi
-----------------------------------
-----------------------------------
Charles Clavadetscher
Neugasse 84
CH - 8005 Zürich
Tel: +41-79-345 18 88
-------------------------------------
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?
I assume that you could declare the column as
address jsonb not null default 'your json here'::jsonb;
I did not try it, but this is what you would do with other data types.
Regards
Charles
I assume that you could declare the column asaddress 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 able to reference to an external JSON file which could be used for the default value.
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.