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

Question

I have this update, i've read postgresql documentation, but nothing clear about how to insert data, some tutorials options:

1.with '{}'
2.with {}
3.with '[]'  <-- array of objects

and most dont' use '::jsonb' like is indicated on:

https://www.postgresql.org/docs/9.4/static/datatype-json.html

here my code:

 UPDATE customer set phones ='{  {"type": "mobile", "phone": "001001"} ,
{"type": "fix", "phone": "002002"}  }'::jsonb  
  where id ='4ca27243-6a55-4855-b0e6-d6e1d957f289';

I get this error:

ERROR:  invalid input syntax for type json
LINE 1: UPDATE customer set phones ='{  {"type": "mobile", "phone": ...
                                    ^
DETAIL:  Expected string or "}", but found "{".
CONTEXT:  JSON data, line 1: {  {...
SQL state: 22P02
Character: 29

I need just record a lit of phones, need to enclose in a big name object like? I mean for javascript , array of objets is not an object, but i dont know if that is accepted in jsonb of postresql

{ phones:[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ] }

Yuci · Accepted Answer · 2019-05-11 13:19:40Z

58

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.

answered May 11, 2019 at 13:19

Yuci

30.7k11 gold badges123 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Irshad Khan Over a year ago

Perfect Answer. can you please tell me how to store point(longitude, latitude) in PostgreSQL

Yuci Over a year ago

@IrshadKhan PostGIS might be your answer, which is a Spatial and Geographic Objects for PostgreSQL: stackoverflow.com/questions/8150721/…

Vao Tsun · Accepted Answer · 2017-11-06 07:03:49Z

9

'{}' 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)

answered Nov 6, 2017 at 7:03

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Example 1 (object):

Example 2 (array):

Notes:

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Example 1 (object):

Example 2 (array):

Notes:

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related