current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite
1

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';
share|improve this question

1 Answer 1

up vote 1 down vote accepted 
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

share|improve this answer
    
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 May 16 '14 at 15:04
    
this works "select id,company_id, data::json#>>'{CUSTA}' from customers;" but it has this unknown column ?column? – Axil May 16 '14 at 15:06
    
add as mycolumn after the '{CUSTA}' to rename the column, like: "select id,company_id, data::json#>>'{CUSTA}' as mycolumn from customers;" – Greg May 16 '14 at 15:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.