insert jsonb data in postgresql, option array or objects, valid way insert jsonb data in postgresql, option array or objects, valid way sql sql

insert jsonb data in postgresql, option array or objects, valid way


Example 1 (object):

CREATE TABLE customer {  contact JSONB}
update customerset 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 customerset phones = '[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ]'where id = '4ca27243-6a55-4855-b0e6-d6e1d957f289';

Notes:

  1. 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
  1. 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)