从...中选择-基于JSON格式的值 [英] select from ... - based on a value in JSON format

问题描述

假设我有一个数据库表，其中包含几个常用列，例如姓名，性别，年龄，...

Let's say I have a database table with several common columns such as name, gender, age, ...

此外，我还有一列使用JSON数据类型(可从Postgres 9.2获得)的列，该列具有JSON中的任意长度和任意字段:

Besides I have an additional column using the JSON datatype (available from Postgres 9.2) with arbitrary length and arbitrary fields in JSON:

{"occupation":"football"}

{"occupation":"football", "hair-colour":"black"}

{"hair-style":"curly"}

使用Postgres 9.3的新功能，我想返回所有带有 occupation = football 的行.

Using new features of Postgres 9.3 I want to return all rows with occupation = football for instance.

类似此伪指令的内容:select * from table where json_field.occupation = football

Something like this pseudo: select * from table where json_field.occupation = football

有没有办法做到这一点?

Is there a way of doing this?

推荐答案

如果我正确理解了手册，则可以使用->和->>运算符访问JSON字段.查询如下:

If I understood the manual correctly, you can access JSON fields with -> and ->> operators. The query would look like:

SELECT *
FROM your_table
WHERE json_field ->> 'occupation' = 'football';

这篇关于从...中选择-基于JSON格式的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！