SQL Server json 查询练习

Posted on Edited on In Views:
JSON 查询

Json 查询

SQL Server json 功能使用

  • 新建数据库 json_test 并使用
1
2
create database json_test
use json_test
  • 每一条 json 的格式如下
1
{ "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" }
  • 将 json 文件修改为标准格式
    • 逗号分隔，最外面加上大括号
1
2
3
4
[
    { "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" },
    { "_id" : "99950", "city" : "KETCHIKAN", "loc" : [ -133.18479, 55.942471 ], "pop" : 422, "state" : "AK" }
]

读成数据项

  • 按照格式新建一个表用于存储
1
2
3
4
5
6
7
8
create table test(
    id int,
    city nvarchar(20),
    locx float,
    locy float,
    pop int,
    state char(10)
)
  • 使用如下命令将数据读入表中
    • OPENJSON
    • OPENROWSET
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare @json NVARCHAR(MAX)

select @json = BulkColumn
from OPENROWSET (BULK 'D:/MyTemp/zips.json', SINGLE_CLOB) as j

insert test
select *
from OPENJSON(@json)
with (
    id int 'strict $._id',
    city nvarchar(30) '$.city',
    locx float '$.loc[0]',
    locy float '$.loc[1]',
    pop int '$.pop',
    state char(10) '$.state'
)
  • 导入数据库之后便可以使用之前简单的查询方式执行了

简单查询

查询 1
  • 简单查看数据库有多少行
1
2
select count(*) from test
-- 29353
查询 2
  • 查询前 6 条记录
    • SQL Server 不支持 limit，使用 top 查询
1
select top 6 * from test

查询 3
  • 统计不同状态（state）的条目
1
2
3
4
select state, count(*) num
from test
group by state
order by num desc
  • 结果如下
    • 一共有 51 种状态
    • 其中最多的是 TX（1671条），最少的是 DC（24）条
    • （条目太多了，这里只显示首尾几条）
1
2
3
4
5
6
7
8
9
10
11
12
13
state      num
---------- -----------
TX                1671
NY                1595
CA                1516
PA                1458
...                ...
HI                  80
RI                  69
DE                  53
DC                  24

(51 行受影响)
查询 4
  • 找到 pop 值最大的 6 个城市
1
2
3
select top 6 *
from test
order by pop desc
  • pop 值最大的是 CHICAGO，值为 112047

读成 json 格式

  • 新建数据表
1
2
3
create table test2 (
    info nvarchar(max)
)
  • 将每一条按照 json 的格式读入
1
2
3
4
5
6
7
8
declare @json nvarchar(max)

select @json = BulkColumn
from OPENROWSET (BULK 'D:/MyTemp/zips.json', SINGLE_CLOB) as j

insert test2
select value as info
from OPENJSON(@json)
  • 重复上面的查询
查询 1
  • 简单查看数据库有多少行
1
2
select count(*) from test2
-- 29353
查询 2
  • 查询前 6 条记录
1
select top 6 * from test2

查询 3
  • 统计不同状态（state）的条目
1
2
3
4
select JSON_VALUE(info, '$.state') state, count(*) num
from test2
group by JSON_VALUE(info, '$.state')
order by num desc
  • 结果是一样的，不过由于格式的原因，显示出来看起来很奇怪
1
2
3
4
5
6
7
8
9
10
11
12
13
state      num
---------- -----------
TX                1671
NY                1595
CA                1516
PA                1458
...                ...
HI                  80
RI                  69
DE                  53
DC                  24

(51 行受影响)
  • 以上的查询方法很慢
  • 优化查询如下（输出是一样的）
1
2
3
4
5
6
7
select state, count(*) num
from (
    select JSON_VALUE(info, '$.state') state
    from test2
) M
group by state
order by num desc
查询 4
  • 找到 pop 值最大的 6 个城市
1
2
3
select top 6 *
from test2
order by JSON_VALUE(info, '$.pop') desc

  • 格式化输出如下
    • 注意这里需要使用 cast 把 pop 转化为 int，否在会被当作字符串，排序失效
1
2
3
4
5
6
select top 6 city, pop
from (
    select JSON_VALUE(info, '$.city') city, cast(JSON_VALUE(info, '$.pop') as int) pop
    from test2
) M
order by pop desc