insert jsonb data in postgresql, option array or objects, valid way
Example 1 (object):
CREATE TABLE customer {
contact JSONB
}
update customer
set contact = '{ "phones":[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ] }'
where id = '4ca27243-6a55-4855-b0e6-d6e1d957f289';
Example 2 (array):
CREATE TABLE customer {
phones JSONB
}
update customer
set phones = '[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ]'
where id = '4ca27243-6a55-4855-b0e6-d6e1d957f289';
Notes:
- My PostgreSQL version
select version();
PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
- Be sure to enclose the keys and values with double quotes.
'{}'
is array type in postgres. if you use jsonb
, use regular '[]'
for array:
so=# select jsonb_pretty('{"phones":[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ] }');
jsonb_pretty
{
"phones": [
{
"type": "mobile",
"phone": "001001"
},
{
"type": "fix",
"phone": "002002"
}
]
}
(1 row)
Time: 0.486 ms
or:
so=# select jsonb_pretty('[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ]');
jsonb_pretty
[
{
"type": "mobile",
"phone": "001001"
},
{
"type": "fix",
"phone": "002002"
}
]
(1 row)