query a json key in postgres json field

Question

Say I have in Postgres stored in JSON field called “data” like this

{
    "CUSTA": {
        "name": "Customer A",
    },
    "CUSTB": {
        "name": "Customer B",
    },
    "CUSTC": {
        "name": "Customer C",
    }
}

How can I query to return the record that contains the key “CUSTA” ? or even better the value of “CUSTA” which is "name": "Customer A"

trying to do something like this but obviously i cant use the keyword key

SELECT * FROM invoices WHERE data->>key = 'CUSTA';

Greg · Accepted Answer · 2014-05-16 14:32:56Z

3

select '{
    "CUSTA": {
        "name": "Customer A"
    },
    "CUSTB": {
        "name": "Customer B"
    },
    "CUSTC": {
        "name": "Customer C"
    }
}'::json#>>'{CUSTA}';
           ?column?
------------------------------
 {                           +
         "name": "Customer A"+
     }
(1 row)

note: you have trailing commas after name:customer x, which is not proper json. For your query, you would probably do something like:

select data#>>'{CUSTA}' from invoices;

or, if data isn't already a json field:

select data::json#>>'{CUSTA}' from invoices;

I don't understand why any invoice would have more than one customer though.

-g

answered May 16, 2014 at 14:32

Greg

6,7892 gold badges29 silver badges43 bronze badges

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

3 Comments

Axil Over a year ago

thanks, that works, actually its not invoice but customer table. so this is the actual sql statement - select data::json#>>'{CUSTA}' from customers; BTW, if i wanted to get the row with all the fields how do i do that ?

Axil Over a year ago

this works "select id,company_id, data::json#>>'{CUSTA}' from customers;" but it has this unknown column ?column?

Greg Over a year ago

add as mycolumn after the '{CUSTA}' to rename the column, like: "select id,company_id, data::json#>>'{CUSTA}' as mycolumn from customers;"

Collectives™ on Stack Overflow

query a json key in postgres json field

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related