admin管理员组

文章数量:1418047

I have a table in PostgreSQL that contains two columns, one being a bigint primary key and the other contains JSONB with a mix of arrays and simple key value pairs.

If I wanted to return the rows in the table where the JSON data in the second column contains a certain exact key value pair, what would the query need to look like? The second column is a JSONB column.

I have been reviewing the PostgreSQL documentation, but I cannot find the operators I would need to make this happen.

I have a table in PostgreSQL that contains two columns, one being a bigint primary key and the other contains JSONB with a mix of arrays and simple key value pairs.

If I wanted to return the rows in the table where the JSON data in the second column contains a certain exact key value pair, what would the query need to look like? The second column is a JSONB column.

I have been reviewing the PostgreSQL documentation, but I cannot find the operators I would need to make this happen.

  • postgresql
  • jsonb
Share Improve this question edited Feb 3 at 5:40 Ken White 126k15 gold badges236 silver badges466 bronze badges asked Feb 3 at 3:52 ma89581ma89581 1
Add a comment  | 

1 Answer 1

Reset to default 1

Your question is not totally clear on that, but if the key-value pair you are looking for is not at the top level of the JSON structure, your best option would be to use a JSONPATH search.

This example searches for a { "key": "value" } pair anywhere nested in a JSON structure:

SELECT JSONB '{
                "x": [
                       { "key": "value" },
                       { "key": "othervalue" }
                     ]
              }'
       @@ 'strict $.**.key == "value"';

本文标签： postgresqlFind row where an exact JSON key value pair existsStack Overflow

版权声明：本文标题：postgresql - Find row where an exact JSON key value pair exists - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1745250205a2649778.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。