postgresql jsonb 使用

jsonb 介绍

postgresql 支持两种 JSON 数据类型：json 和 jsonb 。它们接受几乎 相同的值组作为输入。它们实际的主要差别是效率。json 数据类型存储输入文本的精确拷贝，处理函数必须在每个执行上重新解析； 而 jsonb 数据以分解的二进制格式存储， 这使得它由于添加了转换机制而在输入上稍微慢些，但是在处理上明显更快， 因为不需要重新解析。jsonb 也支持索引，这也是一个明显的优势。因为 json 类型存储输入文本的精确拷贝，它将保存令牌间语义上无关紧要的空格， 和 JSON 对象中键的顺序。另外，如果值中的一个 JSON 对象多次包含相同的键， 那么保存所有的键/值对。（处理函数将最后一个值当做操作值。）相比之下， jsonb 不保存空格，也不保存对象键的顺序，并且不保存重复对象键。 如果在输入中指定了重复的键，那么只保存最后一个值。
官方文件 - json 类型
官方文档 - 支持操作

代码实例

#!usr/bin/env python  
# -*- coding:utf-8 _*-
""" 
@author:cugxy
@file: jsonb_test.py
@time: 2018/10/24
"""

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.postgresql import JSONB
from faker import Faker   # 用于创建假数据


DB_URI = "postgresql://123:123@127.0.0.1:5432/testdb"
engine = create_engine(DB_URI, echo=False)
Base = declarative_base()
Session = sessionmaker(bind=engine)
faker = Faker()


class Test(Base):
	'''
	测试表
	'''
    __tablename__ = 'jsonb_test'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String)
    data = Column(JSONB)

    def __repr__(self):
        return '<jsonb_test %s, %s>' % (self.name, self.data)


def insert_test():
	'''
	测试插入数据 
	'''
    session = Session()
    data = {'a': {'b': {'c': 1, 'd': 2}, 'e': 3}}
    obj = Test(name="a", data=data)
    session.add(obj)
    session.commit()


def insert_test_1000():
	'''
	测试插入 1000 条数据 
	'''
    session = Session()
    for i in range(1000):
        name = i
        n = i % 10 + 2
        data = {}
        for j in range(n):
            value = faker.pystr()
            data[j] = value
        obj = Test(name=name, data=data)
        session.add(obj)
        if i % 100 == 0:
            session.commit()
    session.commit()


def select_test():
	'''
	测试根据普通字段查询
	'''
    session = Session()
    res = session.query(Test).filter(Test.name == '1')
    print(res)
    print(res.all())


def select_json_key_test():
	'''
	测试根据 jsonb 字段 key 值查询
	'''
    session = Session()
    res = session.query(Test).filter(Test.name == '1')
    print(res)
    print(res.all())
    res = session.query(Test.data['0']).filter(Test.name == '1')
    print(res)
    print(res.all())


def select_json_value_test():
	'''
	测试根据 jsonb 字段 value 值查询
	'''
    session = Session()
    res = session.query(Test).filter(Test.name == '1')
    print(res)
    print(res.all())
    # res = session.query(Test).filter(Test.data['a']['b']['c'] == '1')
    res = session.query(Test).filter(Test.data['1']=='"MlMioaCjPrkmXkRdlgdY"') # 字符串 要用外单引号，内部双引号
    print(res)
    print(res.all())


def select_json_null_test():
	'''
	测试查询 jonsb 结果（该 key 不存在）
	'''
    session = Session()
    res = session.query(Test.data['100']).filter(Test.name == '1')
    print(res)
    print(res.all())


if __name__ == '__main__':
    if 0:
        insert_test()
    if 0:
        insert_test_1000()
    if 0:
        select_test()
    if 0:
        select_json_null_test()
    if 0:
        select_json_key_test()
    if 1:
        select_json_value_test()
    pass

注意事项

• 数据库字符编码，推荐使用 utf-8 ，省去不必要的麻烦
• 查询 jsonb 时参数如果为字符串，要用 单引号 加双引号 如：'"test-string"'，其他类型也需要使用 单引号。

总结

很好用，有不完善之处希望大家指出。