1. postgresql 从9.2开始增加对json的支持 9.5 已经可以支持多个json函数了

http://www.postgres.cn/docs/9.5/functions-json.html

2. 创建表，简单查询

create table api(jdoc jsonb);

insert into api values('{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
"name": "Angela Barton",
"is_active": true,
"company": "Magnafone",
"address": "178 Howard Place, Gulf, Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]}');


SELECT jdoc->>'guid' as guid, jdoc->'name' as name FROM api WHERE (jdoc->>'latitude')::float >= 10;
SELECT jdoc->>'guid' as guid, jdoc->'name' as name FROM api  WHERE jdoc->>'name' like '%Angela%';
SELECT jdoc->>'guid' as guid, jdoc->'name' as name FROM api  WHERE jdoc->>'name' = ‘Angela Barton’;

3. 复杂查询

#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
__title__ = ''
__author__ = ''
__mtime__ = '2018/10/18'
"""
from sqlalchemy import Column, String, Integer, JSON, func
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class UserJson(Base):
    __tablename__ = 'user_json'
    id = Column(Integer, primary_key=True)
    content = Column(JSON)

    def __init__(self, content=None):
        self.content = content


from sqlalchemy import create_engine

engine = create_engine('postgresql://test:newpass@localhost:5432/test')

from sqlalchemy.orm import sessionmaker

# Construct a sessionmaker object
session = sessionmaker()

# Bind the sessionmaker to engine
session.configure(bind=engine)

# Create all the tables in the database which are
# defined by Base's subclasses such as User
# Base.metadata.create_all(engine)

data = {
    "code": 0,
    "data": {
        "users": [
            {
                "Age": 33,
                "ID": 1,
                "Url": "http://blog.golang.org/crankshaw@20170502235840",
                "UserName": "Crankshaw@20170502235840"
            },
            {
                "Age": 31,
                "ID": 2,
                "Url": "https://jack.github.io",
                "UserName": "Jack"
            },
            {
                "Age": 22,
                "ID": 3,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 4,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 5,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 6,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 7,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 8,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 9,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 12,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 13,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 14,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 15,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 16,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 17,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 20,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 21,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 22,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 23,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 24,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 25,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 28,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 29,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 30,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 31,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 32,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 33,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 36,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 37,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 38,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 39,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 40,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 41,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 44,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 45,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 46,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 47,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 48,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 49,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 52,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 53,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 54,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 55,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 56,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 57,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 60,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 61,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 62,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 63,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 64,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 65,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 68,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 69,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 70,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 71,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 72,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 73,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 76,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 77,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 78,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 79,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 80,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 81,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 84,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 85,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 86,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 87,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 88,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 89,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 90,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 91,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 92,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 93,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 94,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 95,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            },
            {
                "Age": 22,
                "ID": 98,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 22,
                "ID": 99,
                "Url": "http://blog.golang.org/cock",
                "UserName": "Cock"
            },
            {
                "Age": 21,
                "ID": 100,
                "Url": "http://blog.golang.org/hickinbottom",
                "UserName": "Hickinbottom"
            },
            {
                "Age": 22,
                "ID": 101,
                "Url": "http://blog.golang.org/willy",
                "UserName": "Willy"
            },
            {
                "Age": 25,
                "ID": 102,
                "Url": "http://blog.golang.org/nutter",
                "UserName": "Nutter"
            },
            {
                "Age": 33,
                "ID": 103,
                "Url": "http://blog.golang.org/Pigg",
                "UserName": "Pigg"
            }
        ]
    },
    "msg": "Success"
}
s = session()
# user_json = UserJson()
# user_json.content = data
# s.add(user_json)
# s.commit()
# user_jsons = s.query(UserJson).all()
# for user_json in user_jsons:
#     data['code'] = data['code'] + 1
#     user_json.content = data
#
# s.commit()

# 单个查询
query = s.query(UserJson.content.op('->>')('code')).filter(UserJson.content.op('->>')('code').cast(Integer) == 1).all()
for item in query:
    print(item)

"""
列表查询
"""
subquery = s.query(UserJson.id.label('user_json_id'),
                   func.json_array_elements(UserJson.content['data']['users']).label('users')) \
    .subquery()
query = s.query(UserJson.id, subquery.c.users) \
    .filter(subquery.c.users.op('->>')('Age').cast(Integer) >= 25,
            UserJson.id == subquery.c.user_json_id)
count = 0

for item in query.all():
    count += 1
    print(item)

print(count)

对应的SQL可以有两种写法

SELECT id, obj->>'UserName' as userName,obj->'Age' as age
FROM   user_json vol, json_array_elements(vol.content->'data'->'users') obj
WHERE  (obj->>'Age')::int >= 25;


select user_json.id, table1.users from user_json, (SELECT user_json.id as user_json_id, json_array_elements(user_json.content -> 'data' -> 'users') AS users
FROM user_json) AS table1  WHERE (table1.users->>'Age')::int >= 25 and user_json.id = table1.user_json_id;

4. json和jsonb的区别

而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。

5. 参考链接

https://docs.sqlalchemy.org/en/latest/core/type_basics.html#generic-types

http://www.postgres.cn/docs/9.5/functions-json.html

GitHub 加速计划 / js / json
41.72 K
6.61 K
下载
适用于现代 C++ 的 JSON。
最近提交(Master分支：1 个月前 )
960b763e 2 个月前
8c391e04 5 个月前
Logo
GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

[转载]在Windows环境下安装GNU Radio

转自：在Windows环境下安装GNURadio_恐弱智_新浪博客GNU Radio是用Python开发的，大部分开源的工程能够在Linux环境下运行良好，而Windows下却运行的很勉强，而且安装配置都很复杂。GNU Radio算是个例外了，不光提供了Windows的二进制安装，还有比较详细的说明。我是Python小白，所以折腾了好久才弄好，特意记录下来，免得以后再装还折腾。GNU Radio的

avatar GitCode 开源社区

centOS 8 使用dnf安装Docker

DNF是什么？CentOS 8使用YUM软件包管理器版本v4.0.4。现在，该版本使用DNF(已删除YUM)。DNF是软件包管理器。它会在Linux发行版上安装，执行更新并删除软件包。使用DNF安装Docker跳过具有损坏依赖性的程序包一个有效的解决方案是使您的CentOS 8系统使用以下--nobest命令安装最符合条件的版本：sudo dnf install docker...

avatar GitCode 开源社区

定时同步数据库表(mysql+linux+crontab)

sync.sh里面的参数需要改变，ip/username/password/database/tablesync.sh#!/bin/sh# Please change the IP and password of the data source db.# Then change the table name.filename=/home/nington/db/$(date +%Y-%m

avatar GitCode 开源社区