Postgresql中JSON数据构造与操作符实例

news/2024/7/9 21:23:30 标签: postgresql, json, 数据库, jsonpath

JSON数据构造方法

注意：区分数字与文本。

  • 这是数字：'0.1'::json
  • 这是文本：'"0.1"'::json
  • 1是数组，3是文本：'[1, "3"]'::jsonb

注意：区分数据的三种形态。

  • 这是单独数据：'1'::json
  • 这是数组里面的数据：'[1]'::json
  • 这是object里面的数据：'{"abc":1}'::json
-- 可以为字符串，必须用双引号
SELECT '"abc"'::json;			-- OK
 json  
-------
 "abc"
 
-- 可以为数字
SELECT '0.1'::json;				-- OK
 json 
------
 0.1
 
SELECT '1e100'::json;			-- OK
 json  
-------
 1e100

-- 可以为true/false/null
SELECT ' true '::json;			-- OK, even with extra whitespace
  json  
--------
  true 
 
-- 可以为数组
SELECT '[]'::json;				-- OK
 json 
------
 []
 
SELECT '[1,2]'::json;			-- OK
 json  
-------
 [1,2]
 
-- 可以为Objects：{key:value}
SELECT '{}'::json;				-- OK
 json 
------
 {}
 
SELECT '{"abc":1}'::json;		-- OK
   json    
-----------
 {"abc":1}
 
 
-- 可以嵌套
SELECT '{"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}'::json; -- OK
                          json                           
---------------------------------------------------------
 {"abc":1,"def":2,"ghi":[3,4],"hij":{"klm":5,"nop":[6]}}

JSON操作符与实例

  • JSON与JSONB的差别在存储上，JSON直接保存文本不做任何解析；JSONB在输入后自动解析记录二进制信息。在使用时JSONB省去了解析的步骤，使用时效率更高；JSONB支持索引；建议生产中使用JSONB。
  • 注意双引号和单引号的使用，在json中最外层使用单引号后，需要内部使用双引号。

1 适用于JSON/JSONB

操作符实例结果
->整数：选择数组中元素：输出jsonselect ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json -> 2;{“c”:“baz”}
->整数：选择数组中元素：输出jsonselect ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json -> -3;{“a”:“foo”}
->text：选择object中的元素：输出jsonselect ‘{“a”: {“b”:“foo”}}’::json -> ‘a’;{“b”:“foo”}
->>整数：选择数组中元素：输出textselect ‘[1,2,3]’::json ->> 2;3
->>text：选择object中的元素：输出textselect ‘{“a”:1,“b”:2}’::json ->> ‘b’;2
#>路径：按路径选择元素：输出jsonselect ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #> ‘{a,b,1}’;“bar”
select ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #> ‘{a,b}’;[“foo”,“bar”]
select ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #> ‘{a,c}’;空：路径不通
#>>路径：按路径选择元素：输出textselect ‘{“a”: {“b”: [“foo”,“bar”]}}’::json #>> ‘{a,b,1}’;bar

（#>>多条路径只能返回最后匹配的一个）

2 适用于JSONB

子集、包含、组合

操作符实例结果
A@>B：B是A的子集？：输出boolselect ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb;t
select ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2, “a”:1}’::jsonb;t
select ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2, “a”:2}’::jsonb;f
select ‘{“a”:1, “b”:2}’::jsonb @> ‘{}’::jsonb;t
select ‘{“a”:1, “b”:2}’::jsonb @> ‘[]’::jsonb;f
select ‘{“a”:1, “b”:2}’::jsonb @> ‘null’::jsonb;f
A<@B：A是B的子集？：输出boolselect ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb;t
?text：顶级key或数组包含text？：输出boolselect ‘{“a”:1, “b”:2}’::jsonb ? ‘b’;t
select ‘[“a”, “b”, “c”]’::jsonb ? ‘b’;t
?|text[]：顶级key或数组包含text[]中的任意一个？：输出boolselect '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'];t
?&text[]：顶级key或数组包含text[]中的全部？：输出boolselect '["a", "b", "c"]'::jsonb ?& array['a', 'b'];t
||：组合数组：输出jsonbselect '["a", "b"]'::jsonb || '["a", "d"]'::jsonb;[“a”, “b”, “a”, “d”]
||：组合object（自动合并）：输出jsonbselect '{"a": "b"}'::jsonb || '{"c": "d"}'::jsonb;{“a”: “b”, “c”: “d”}
select '{"a": "b"}'::jsonb || '{"a": "d"}'::jsonb;{“a”: “d”}
||：组合数组与元素：输出jsonbselect '[1, 2]'::jsonb || '3'::jsonb;[1, 2, 3]
||：组合object与元素：输出jsonbselect '{"a": "b"}'::jsonb || '42'::jsonb;[{“a”: “b”}, 42]
||：组合object与数组：输出jsonbselect '{"a": "b"}'::jsonb || '[1, 2]'::jsonb;[{“a”: “b”}, 1, 2]

删除

操作符实例结果
-text：删除key或数组元素：输出jsonbselect ‘{“a”: “b”, “c”: “d”}’::jsonb - ‘a’;{“c”: “d”}
select ‘[“a”, “b”, “c”, “b”]’::jsonb - ‘b’;[“a”, “c”]
-text[]：删除key或数组元素：输出jsonbselect ‘{“a”: “b”, “c”: “d”}’::jsonb - ‘{a,c}’::text[];{}
select ‘[“a”, “b”, “c”]’::jsonb - 0;[“b”, “c”]
select ‘[“a”, “b”, “c”]’::jsonb - 1;[“a”, “c”]
select ‘[“a”, “b”, “c”]’::jsonb - -1;[“a”, “b”]
select ‘[“a”, “b”, “c”]’::jsonb - -2;[“a”, “c”]
select ‘[“a”, “b”, “c”]’::jsonb - -3;[“b”, “c”]
总结：正数位置[0,1,2]，负数位置[-3,-2,-1]
#-text[]：按路径删除：输出jsonbselect ‘[“a”, {“b”:1}]’::jsonb #- ‘{1,b}’;[“a”, {}]

涉及jsonpath的操作符：@? jsonpath、@@ jsonpath，请先阅读下面一章：JSONPATH

操作符实例结果
@? jsonpath：给出的jsonpath是否能找到元素select '{"a":[1,2,3,4,5]}'::jsonb @? '$.a[*] ? (@ > 2)';t
@? jsonpath：给出的jsonpath找到结果集，能否满足jsonpath中的布尔表达式？select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2';t
select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 5';f

JSONPATH

jsonpath是Postgresql12引入的特性，类似于使用XPATH的方式访问XML，参考了一些JS的语法引入的一种描述数据的语法。

例如下面一段json

{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}

使用上面介绍的操作符#>> '{track,segments}'可以拿到segments数组值。

$.track.segments

select '{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb #>> '{track,segments}';

-- 结果
[
  {"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, 
  {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
]

也可以使用Jsonpath语言拿到segments数组：

select jsonb_path_query('{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb,
'$.track.segments');

-- 结果
[
  {"HR": 73, "location": [47.763, 13.4034], "start time": "2018-10-14 10:05:14"}, 
  {"HR": 135, "location": [47.706, 13.2635], "start time": "2018-10-14 10:39:21"}
]

再看两个例子：

$.track.segments[*].location

select jsonb_path_query('{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb,
'$.track.segments[*].location');

-- 结果
[47.763, 13.4034]
[47.706, 13.2635]


// `$.track.segments[0].location`

select jsonb_path_query('{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb,
'$.track.segments[0].location');

-- 结果
[47.763, 13.4034]

$.track.segments[*].HR ? (@ > 130)

@代指.最后的元素

select jsonb_path_query('{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb,
'$.track.segments[*].HR ? (@ > 130)');

-- 结果
135

$.track.segments[*] ? (@.HR > 130)."start time"

这里@代指segments数组

select jsonb_path_query('{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb,
'$.track.segments[*] ? (@.HR > 130)."start time"');

-- 结果
"2018-10-14 10:39:21"

$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"

select jsonb_path_query('{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}'::jsonb,
'$.track.segments[*] ? (@.location[1] < 13.4) ? (@.HR > 130)."start time"');

-- 结果
"2018-10-14 10:39:21"

http://www.niftyadmin.cn/n/9246.html

相关文章

Vue3生命周期函数

Vue3生命周期函数

Vue3生命周期函数与vue2的对比&#xff1a; Vue3的setup语法糖直接代替beforeCreate&#xff0c;Created&#xff1b;Destroy更名为Unmount&#xff1b;其他就是直接加on beforeCreate -> 使用 setup() created -> 使用 setup() beforeMount -> onBeforeMount d…
阅读更多...
基于正交投影的实时三维人体姿态估计

基于正交投影的实时三维人体姿态估计

王亦洲课题组 ECCV 2022 入选论文解读&#xff1a;基于正交投影的实时三维人体姿态估计 本文是对发表于计算机视觉领域顶级会议 ECCV 2022的论文 Faster VoxelPose: Real-time 3D Human Pose Estimation by Orthographic Projection 的解读。该论文由北京大学王亦洲课题组与微软…
阅读更多...
HTML西安旅游网页设计作业成品 大学生旅游风景区网页设计作业模板下载 静态HTML旅游景点网页制作下载 DW网页设计代码

HTML西安旅游网页设计作业成品 大学生旅游风景区网页设计作业模板下载 静态HTML旅游景点网页制作下载 DW网页设计代码

&#x1f468;‍&#x1f393;学生HTML静态网页基础水平制作&#x1f469;‍&#x1f393;&#xff0c;页面排版干净简洁。使用HTMLCSS页面布局设计,web大学生网页设计作业源码&#xff0c;这是一个不错的旅游网页制作&#xff0c;画面精明&#xff0c;排版整洁&#xff0c;内容…
阅读更多...
CSS引入方式 Emmet语法 CSS复合选择器

CSS引入方式 Emmet语法 CSS复合选择器

&#x1f353;个人主页&#xff1a;bit.. &#x1f352;系列专栏&#xff1a;Linux(Ubuntu)入门必看 C语言刷题 数据结构与算法 HTML和CSS3 目录 一.CSS的撒比中样式表 1.2 内部样式表 1.3行内样式表 1.4外部样式表 1.5CSS引入方式的总结 二.Emmet语法 2.1快速生…
阅读更多...
JPA Buddy指南

JPA Buddy指南

1. 概述 JPA Buddy是一个广泛使用的IntelliJ IDEA插件&#xff0c;面向使用JPA数据模型和相关技术&#xff08;如Spring DataJPA&#xff0c;DB版本控制工具&#xff08;Flyway&#xff0c;Liquibase&#xff09;&#xff0c;MapStruct等&#xff09;的新手和有经验的开发人员。…
阅读更多...
jdk 下载 ，开发工具下载 [jdk1.8.0_251.zip]

jdk 下载 ，开发工具下载 [jdk1.8.0_251.zip]

jdk 下载 &#xff0c;开发工具下载 jdk1.8.0_251.zip
阅读更多...
【毕业设计】深度学习身份证识别系统 - 机器视觉 python

【毕业设计】深度学习身份证识别系统 - 机器视觉 python

文章目录0 前言1 实现方法1.1 原理1.1.1 字符定位1.1.2 字符识别1.1.3 深度学习算法介绍1.1.4 模型选择2 算法流程3 部分关键代码4 效果展示5 最后0 前言 &#x1f525; Hi&#xff0c;大家好&#xff0c;这里是丹成学长的毕设系列文章&#xff01; &#x1f525; 对毕设有任…
阅读更多...
若依3.x.x版本环境搭建

若依3.x.x版本环境搭建

一、3.6.1 1. nacos 1. nacos-v2.1.2 1.docker docker run \ --name nacos-v2.1.2 -d \ -p 8848:8848 -p 9848:9848 -p 9849:9849 \ --privilegedtrue \ --restartalways \ -e JVM_XMS256m \ -e JVM_XMX256m \ -e MODEstandalone \ -e PREFER_HOST_MODEip \ -e SPRING_DAT…
阅读更多...
最新文章

Copyright @ 2022~2023