PostgreSQL-JSON 数据类型

最新推荐文章于 2025-09-13 21:59:18 发布

weixin_30548917

最新推荐文章于 2025-09-13 21:59:18 发布

阅读量121

点赞数

CC 4.0 BY-SA版权

文章标签： json 数据库 ruby

原文链接：http://www.cnblogs.com/cloudtj/articles/6193986.html

PostgreSQL-JSON数据类型

前言

从PostgreSQL 9.3版本开始，JSON已经成为内置数据类型，“一等公民”啦。

还在羡慕什么文档数据库或者BSON么，赶紧玩玩吧。另外9.4版本，提供JSONB（Binary），提供更多JSON函数和索引支持。

常用的读取操作符目前大概有三类：->、->>和#>。还是直接看SQL查询的例子吧。

1、先看->例子

    postgres=# select '[1,2,3]'::json->2;
     ?column?
    ----------
     3
    (1 row)
    
    postgres=# select '{"a":1,"b":2}'::json->'b';
     ?column?
    ----------
     2
    (1 row)

2、再来->>例子

    postgres=# select '[1,2,3]'::json->>2;
     ?column?
    ----------
     3
    (1 row)
    
    postgres=# select '{"a":1,"b":2}'::json->>'b';
     ?column?
    ----------
     2
    (1 row)

    有没有发现其实->和->>出来的结果肉眼看起来是一样的？区别在于后者是返回text。

3、再来#>例子

上面两个操作符实现了读取，其实大部分时候我们的JSON不是这么简单，会内嵌各种数组和哈希，这么读下去会死人的吧。当然，有一种类似path的读取，看例子吧：

    postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}';
     ?column?
    ----------
     3
    (1 row)
    
    postgres=# select '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';
     ?column?
    ----------
     3
    (1 row)


    # 当然里面可以嵌套很多，比如{a,2,b,3}等等。下面再来点表的例子：
    postgres=# create table testjson(id serial, data json);
    postgres=# insert into testjson (data) values('{"a": 1, "b": 2}'::json);
    postgres=# insert into testjson (data) values('{"a": 3, "b": 4, "c": 5}'::json);
    postgres=# insert into testjson (data) values('{"a": 6, "c": 7}'::json);
    
    
    # 插入数据是不是很熟悉，基本和普通使用JSON一致，初窥下select结果：
    postgres=# select * from testjson;
     id |           data
    ----+--------------------------
      1 | {"a": 1, "b": 2}
      2 | {"a": 3, "b": 4, "c": 5}
      3 | {"a": 6, "c": 7}
    (3 rows)
    
            
    # 很眼熟，where条件可以这么用：
    postgres=# select * from testjson where (data->>'a')::int>1;
     id |           data
    ----+--------------------------
      2 | {"a": 3, "b": 4, "c": 5}
      3 | {"a": 6, "c": 7}
    (2 rows)
    # 注意这里是->>转换成text然后在::int进行比较。
    
    
    # 不过这里有个坑，不知道怎么解决，比如：
    postgres=# insert into testjson (data) values('{"a": "smallfish"}');
    postgres=# select * from testjson;
     id |           data
    ----+--------------------------
      1 | {"a": 1, "b": 2}
      2 | {"a": 3, "b": 4, "c": 5}
      3 | {"a": 6, "c": 7}
      5 | {"a": "smallfish"}
    (4 rows)
    # 这个时候上面的::int>1这样就报错了，因为最后一行a为字符串。

4、row_to_json的妙用

The simplest way to return JSON is with row_to_json() function. It accepts a row value and returns a JSON value.

    select row_to_json(words) from words;

This will return a single column per row in the words table.

    {"id":6013,"text":"advancement","pronunciation":"advancement",...}

However, sometimes we only want to include some columns in the JSON instead of the entire row. In theory we could use the row constructor method.

    select row_to_json(row(id, text)) from words;

While this does return only the id and text columns, unfortunately it loses the field names and replaces them with f1, f2, f3, etc.

    {"f1":6013,"f2":"advancement"}

To work around this we must either create a row type and cast the row to that type or use a subquery. A subquery will typically be easier.

    select row_to_json(t)
    from (
      select id, text from words
    ) t

This results in the JSON output for which we would hope:

    {"id":6013,"text":"advancement"}

The other commonly used technique is array_agg and array_to_json. array_agg is a aggregate function like sum or count. It aggregates its argument into a PostgreSQL array. array_to_json takes a PostgreSQL array and flattens it into a single JSON value.

    select array_to_json(array_agg(row_to_json(t)))
    from (
      select id, text from words
    ) t

This will result in a JSON array of objects:

    [{"id":6001,"text":"abaissed"},{"id":6002,"text":"abbatial"},{"id":6003,"text":"abelia"},...]

In exchange for a substantial jump in complexity, we can also use subqueries to return an entire object graph:

    select row_to_json(t)
    from (
      select text, pronunciation,
        (
          select array_to_json(array_agg(row_to_json(d)))
          from (
            select part_of_speech, body
            from definitions
            where word_id=words.id
            order by position asc
          ) d
        ) as definitions
      from words
      where text = 'autumn'
    ) t

This could return a result like the following:

    {
      "text": "autumn",
      "pronunciation": "autumn",
      "definitions": [
        {
            "part_of_speech": "noun",
            "body": "skilder wearifully uninfolded..."
        },
        {
            "part_of_speech": "verb",
            "body": "intrafissural fernbird kittly..."
        },
        {
            "part_of_speech": "adverb",
            "body": "infrugal lansquenet impolarizable..."
        }
      ]
    }

Obviously, the SQL to generate this JSON response is far more verbose than generating it in Ruby. Let's see what we get in exchange.