Json postgresql 9.4

Question

I have tried and facing issue while reading json column in PostgreSQL table. below is the table description.

task=# \d quots                                           
       Table "public.quots"
      Column      |  Type   | Modifiers 
------------------+---------+-----------
 items            | json    | 
 id               | integer | 
 supplier_id      | integer |

Here is the data in table.

select items from quots limit 1;
                                                    items
-----------------------------------------------------------------------------------------------------------------------------------
 [{"item1":[{"id":5,"name":"Item Name","unitPrice":{"original":10.0,"converted":10.0}}],"item2":{"id":75,"name":"Name"},"id":23653}]

Now when I try to query and get item1, it return empty.

select items->'item1' from quots limit 1;
 ?column? 
----------

(1 row)

Any Clue?

PostgreSQL 9.4.4 PSQL 9.4.4

klin · Accepted Answer · 2017-02-16 21:20:01Z

2

The json value is an array. You can select its first element using items->0:

select items->0->'item1' from quots limit 1;

                                   ?column?                                   
------------------------------------------------------------------------------
 [{"id":5,"name":"Item Name","unitPrice":{"original":10.0,"converted":10.0}}]
(1 row)

answered Feb 16, 2017 at 21:20

klin

123k15 gold badges241 silver badges263 bronze badges

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

2 Comments

sharafjaffri Over a year ago

When I try to use array_to_json(items->0->'item1') it returns. ERROR: cannot deconstruct an array as an object

klin Over a year ago

I can not help you without seeing the context. Ask a new question containing data causing the error.

Collectives™ on Stack Overflow

Json postgresql 9.4

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related