在postgres json字段中查询json键

问题描述

说我在Postgres中像这样存储在称为数据"的JSON字段中

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",
    }
}

如何查询以返回包含键"CUSTA"的记录?甚至更好的是"CUSTA"的值，即名称":客户A"

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"

试图做这样的事情，但显然我不能使用关键字key

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

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

推荐答案

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

注意:name:customer x后跟逗号，这不是正确的json.对于您的查询，您可能会做类似的事情:

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;

或者，如果数据还不是json字段:

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

这篇关于在postgres json字段中查询json键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！