Postgresql关于JSON、JSONB的操作符、处理函数(持续更新)

这篇具有很好参考价值的文章主要介绍了Postgresql关于JSON、JSONB的操作符、处理函数(持续更新)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、postgresql实用函数

(只列举本次用到的函数,其他函数在文章后面有详解)

1.1、将jsonb格式的数组中的值展开/分解成单独的数据/对象?

Postgresql关于JSON、JSONB的操作符、处理函数(持续更新)

SELECT answer_id,  jsonb_array_elements(option_ids)::INTEGER  AS option_id

FROM db_live_answer 

WHERE survey_code='No.20230605165428002'

 Postgresql关于JSON、JSONB的操作符、处理函数(持续更新)

 

1.2、如何根据对象id将jsonb数组展开到每个jsonb列的对象中?

Postgresql关于JSON、JSONB的操作符、处理函数(持续更新)

 如果所有json数组都包含两个新列的两个元素,那么使用固定路径,如dmfay的答案。 否则,你应该使用jsonb_array_elements()两次取消数组,分别为text_id_onetext_id_two

update sample t set
    text_id_one = value1,
    text_id_two = value2
from sample s, 
    jsonb_array_elements(s.metadata_array) as e1(value1),
    jsonb_array_elements(s.metadata_array) as e2(value2)
where s.id = t.id
    and value1->>'id' = 'textIdOne'
    and value2->>'id' = 'textIdTwo'
returning t.*

如果数组中有两个以上的元素,则此变体可能更高效(也更方便):

update sample t
set 
    text_id_one = arr1->0,
    text_id_two = arr2->0
from (
    select 
        id,
        jsonb_agg(value) filter (where value->>'id' = 'textIdOne') as arr1,
        jsonb_agg(value) filter (where value->>'id' = 'textIdTwo') as arr2
    from sample,
    jsonb_array_elements(metadata_array)
    group by id
    ) s
where t.id = s.id
returning t.*

例子来源:如何根据对象id将jsonb数组展开到每个jsonb列的对象中?

2、postgresql操作符的使用

一、 -> 和 ->>  (适用于操作数组) :

-> 表示获取一个JSON数组元素,支持下标值(下标从0开始)、Key获取。

->> 表示获取一个JSON对象字符串。

代码:
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1;
结果:
{"b":2}
以上,::JSON 表示声明前面的字符串为一个JSON字符串对象,而且PostgreSQL中的JSON、JSONB对象 Key的声明必须是字符串 。同时,1表示获取JSON数组中下标值为1的JSON对象。

接下来,看下 ->> 的用法:

代码:
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1;
结果:
{"b":2}
以上,->> 的查询结果和 -> 对比不太直观,我们可以进一步验证。

连用:

代码:
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1 -> 'b';
结果:
2
以上,我们可以看到首先我们使用下标的方式,获取JSON数组中下标值为1的JSON对象 {"b":2}。然后,我们通过Key的方式来获取这个JSON对象的Value值,结果是 2。

接下来,我们测试下 ->> 的方式来获取:

代码:
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
 
报错:
[SQL]SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
 
[Err] 错误:  操作符不存在: text -> unknown
LINE 1: SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1 -> 'b';
                                                       ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
以上,可以看到错误提示操作符不存在,因为第一步查询出来的是一个字符串,不是JSON对象。

改成正确提取方式:

代码:
SELECT ('[{"a":1},{"b":2},{"c":3}]'::JSON ->> 1)::JSON -> 'b';
结果:
2
以上,可以看到现在可以正确查询出结果,我们将第一步查询出来的字符串转成JSON对象,然后通过 Key 的方式来获取 Value。不过,这种查询方式相对于 -> 来说还是比较繁琐的。

二、 #> 和 #>> (适用于操作对象)

在前一步,我们在一个JSON数组中可以使用 -> 下标值的方式来获取一个JSON对象。但是,如果我们我们检索的不是JSON数组,而是一个JSON对象中的JSON对象。很显然,这种下标获取的方式不再适用。不过,我们可以使用下面的方式来获取。

#> 表示获取指定路径的一个JSON对象,

#>>表示获取指定路径的一个JSON对象的字符串。

代码:
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON #> '{b}'
结果:
{"ba":"b1","bb":"b2"}
以上,我们使用 #> 方式来获取一个JSON对象中的JSON对象。

注意:

在获取一个JSON对象时,除非是JSON数组中的下标,必须要要用 { } 将JSON对象的 Key 包裹起来,否则会抛出异常。

代码:
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'
结果:
[Err] 错误:  有缺陷的数组常量:"b"
LINE 1: SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'b'
                                                                ^
DETAIL:  数组值必须以 "{" 或者维度信息开始。

同样的,我们还可以在 #> 的基础上,继续获取这个JSON对象内的相关信息。

代码:
SELECT '{"a":1,"b":{"ba":"b1","bb":"b2"},"c":3}'::JSON#>'{b}'->'ba'
结果:
"b1"
​​​​​​以上,可以看到 -> 获取的是一个JSON对象。->>是获取的一个text 文本


二、PostgreSQL 操作符、函数

json和jsonb 操作符

操作符 右操作数类型 描述 例子 例子结果
-> int 获得 JSON 数组元素(索引从 0 开始,负整数从末尾开始计) [{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 {"c":"baz"}
-> text 通过键获得 JSON 对象域 {"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int 以text形式获得 JSON 数组元素 [1,2,3]'::json->>2 3
->> text 以text形式获得 JSON 对象域 {"a":1,"b":2}'::json->>'b' 2
#> text[] 获取在指定路径的 JSON 对象 {"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c": "foo"}
#>> text[] 以text形式获取在指定路径的 JSON 对象 {"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}' 3

额外的jsonb操作符

操作符 右操作数类型 描述 例子
@> jsonb 左边的 JSON 值是否在顶层包含右边的 JSON 路径/值项? {"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@ jsonb 左边的 JSON 路径/值项是否被包含在右边的 JSON 值的顶层? {"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
? text 键/元素字符串是否存在于 JSON 值的顶层? {"a":1, "b":2}'::jsonb ? 'b'
?| text[] 这些数组字符串中的任何一个是否做为顶层键存在? {"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?& text[] 是否所有这些数组字符串都作为顶层键存在? ["a", "b"]'::jsonb ?& array['a', 'b']
|| jsonb 把两个jsonb值串接成一个新的jsonb值 ["a", "b"]'::jsonb || '["c", "d"]'::jsonb
- text 从左操作数删除键/值对或者string 元素。键/值对基于它们的键值来匹配。 {"a": "b"}'::jsonb - 'a'
- text[] 从左操作数中删除多个键/值对或者string元素。键/值对基于它们的键值来匹配。 {"a": "b", "c": "d"}'::jsonb - '{a,c}'::text[]
- integer 删除具有指定索引(负值表示倒数)的数组元素。如果 顶层容器不是数组则抛出一个错误。 ["a", "b"]'::jsonb - 1
#- text[] 删除具有指定路径的域或者元素(对于 JSON 数组,负值 表示倒数) ["a", {"b":1}]'::jsonb #- '{1,b}'

JSON 创建函数

函数 描述 例子 例子结果
to_json(anyelement)  to_jsonb(anyelement)     把该值返回为json或者jsonb。数组和组合 会被(递归)转换成数组和对象;对于不是数组和组合的值,如果有 从该类型到json的造型,造型函数将被用来执行该 转换;否则将产生一个标量值。对于任何不是数字、布尔、空值的标 量类型,将使用文本表达,在这种风格下它是一个合法的 json或者jsonb值。 to_json('Fred said "Hi."'::text) Fred said \Hi.\""
array_to_json(anyarray [, pretty_bool])     把数组作为一个 JSON 数组返回。一个 PostgreSQL 多维数组会成为一个数组 的 JSON 数组。如果pretty_bool为真,将在 第 1 维度的元素之间增加换行。 array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
row_to_json(record [, pretty_bool])     把行作为一个 JSON 对象返回。如果pretty_bool为真,将在第1层元素之间增加换行。 row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}
json_build_array(VARIADIC "any")   jsonb_build_array(VARIADIC "any")     从一个可变参数列表构造一个可能包含异质类型的 JSON 数组。 json_build_array(1,2,'3',4,5) [1, 2, "3", 4, 5]
json_build_object(VARIADIC "any")   jsonb_build_object(VARIADIC "any")     从一个可变参数列表构造一个 JSON 对象。通过转换,该参数列表由交替 出现的键和值构成。 json_build_object('foo',1,'bar',2) {"foo": 1, "bar": 2}
json_object(text[])  jsonb_object(text[])     从一个文本数组构造一个 JSON 对象。该数组必须可以是具有偶数个成员的 一维数组(成员被当做交替出现的键/值对),或者是一个二维数组(每一个 内部数组刚好有 2 个元素,可以被看做是键/值对)。 json_object('{a, 1, b, "def", c, 3.5}')  json_object('{{a, 1},{b, "def"},{c, 3.5}}')     {"a": "1", "b": "def", "c": "3.5"}

json_object(keys text[], values text[]) 

jsonb_object(keys text[], values text[])

 json_object的这种形式从两个独立的数组得到键/值对。在其 他方面和一个参数的形式相同。 json_object('{a, b}', '{1,2}') {"a": "1", "b": "2"}

JSON 处理 

函数 返回值 描述 例子 例子结果
json_array_length(json)  jsonb_array_length(jsonb) int 返回最外层 JSON 数组中的元素数量。 json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]') 5
json_each(json) 
jsonb_each(jsonb)
setof key text, value json
setof key text, value jsonb
扩展最外层的 JSON 对象成为一组键/值对。 select * from json_each('{"a":"foo", "b":"bar"}')  key | value
-----+-------
 a   | "foo"
 b   | "bar"
json_each_text(json)  jsonb_each_text(jsonb) setof key text, value text 扩展最外层的 JSON 对象成为一组键/值对。返回值将是text类型。 select * from json_each_text('{"a":"foo", "b":"bar"}')  key | value
-----+-------
 a   | foo
 b   | bar
json_extract_path(from_json json, VARIADIC path_elems text[])   jsonb_extract_path(from_json jsonb, VARIADIC path_elems text[]) json
jsonb
返回由path_elems指向的 JSON 值(等效于#>操作符)。 json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4') {"f5":99,"f6":"foo"}
json_extract_path_text(from_json json, VARIADIC path_elems text[])  jsonb_extract_path_text(from_json jsonb, VARIADIC path_elems text[])  text 以text返回由path_elems指向的 JSON 值(等效于#>>操作符)。 json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6') foo
json_object_keys(json)  jsonb_object_keys(jsonb) setof text 返回最外层 JSON 对象中的键集合。 json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}') json_object_keys
------------------
 f1
 f2
json_populate_record(base anyelement, from_json json)  jsonb_populate_record(base anyelement, from_json jsonb) anyelement 扩展from_json中的对象成一个行,它的列匹配由base定义的记录类型(见下文的注释)。 select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", "a b"], "c": {"d": 4, "e": "a b c"}}')  a |   b       |      c
---+-----------+-------------
 1 | {2,"a b"} | (4,"a b c")
json_populate_recordset(base anyelement, from_json json)  jsonb_populate_recordset(base anyelement, from_json jsonb) setof anyelement 扩展from_json中最外的对象数组为一个集合,该集合的列匹配由base定义的记录类型。 select * from json_populate_recordset(null::myrowtype, '[{"a":1,"b":2},{"a":3,"b":4}]')  a | b
---+---
 1 | 2
 3 | 4
json_array_elements(json)  jsonb_array_elements(jsonb) setof json
setof jsonb
把一个 JSON 数组扩展成一个 JSON 值的集合。 select * from json_array_elements('[1,true, [2,false]]') value
-----------
 1
 true
 [2,false]
json_array_elements_text(json)  jsonb_array_elements_text(jsonb) setof text 把一个 JSON 数组扩展成一个text值集合。 select * from json_array_elements_text('["foo", "bar"]')   value
-----------
 foo
 bar
json_typeof(json)  jsonb_typeof(jsonb) text 把最外层的 JSON 值的类型作为一个文本字符串返回。可能的类型是: object、array、string、number、 boolean以及null。 json_typeof('-123.4') number
json_to_record(json)  jsonb_to_record(jsonb) record 从一个 JSON 对象(见下文的注解)构建一个任意的记录。正如所有返回record 的函数一样,调用者必须用一个AS子句显式地定义记录的结构。 select * from json_to_record('{"a":1,"b":[1,2,3],"c":[1,2,3],"e":"bar","r": {"a": 123, "b": "a b c"}}') as x(a int, b text, c int[], d text, r myrowtype)  a |    b    |    c    | d |       r
---+---------+---------+---+---------------
 1 | [1,2,3] | {1,2,3} |   | (123,"a b c")
json_to_recordset(json)  jsonb_to_recordset(jsonb) setof record 从一个 JSON 对象数组(见下文的注解)构建一个任意的记录集合。正如所有返回record 的函数一样,调用者必须用一个AS子句显式地定义记录的结构。 select * from json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]') as x(a int, b text);  a |  b
---+-----
 1 | foo
 2 |
json_strip_nulls(from_json json)  jsonb_strip_nulls(from_json jsonb) json
jsonb
返回from_json,其中所有具有空值的 对象域都被省略。其他空值不动。 json_strip_nulls('[{"f1":1,"f2":null},2,null,3]') [{"f1":1},2,null,3]
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) jsonb 返回target,其中由 path指定的节用 new_value替换,如果 path指定的项不存在并且 create_missing为真(默认为 true)则加上 new_value。正如面向路径的 操作符一样,出现在path中的 负整数表示从 JSON 数组的末尾开始数。 jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false) jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]') [{"f1":[2,3,4],"f2":null},2,null,3][{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean]) jsonb 返回被插入了new_value的target。如果path指定的target节在一个 JSONB 数组中,new_value将被插入到目标之前(insert_after为false,默认情况)或者之后(insert_after为真)。如果path指定的target节在一个 JSONB 对象内,则只有当target不存在时才插入new_value。对于面向路径的操作符来说,出现在path中的负整数表示从 JSON 数组的末尾开始计数。 jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"')  jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"', true) {"a": [0, "new_value", 1, 2]}  {"a": [0, 1, "new_value", 2]}
jsonb_pretty(from_json jsonb) text 把from_json返回成一段 缩进后的 JSON 文本。 jsonb_pretty('[{"f1":1,"f2":null},2,null,3]') [
    {
        "f1": 1,
        "f2": null
    },
    2,
    null,
    3
]

 参考:PostgreSQL中JSON函数和操作符_function jsonb_set(json, unknown, unknown) does no_EngineerForSoul的博客-CSDN博客

postgresql 关于JSON、JSONB操作符以处理函数_postgresql json函数_谷满满的博客-CSDN博客文章来源地址https://www.toymoban.com/news/detail-477774.html

到了这里,关于Postgresql关于JSON、JSONB的操作符、处理函数(持续更新)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • PostgreSQL 查询json/jsonb是否存在某个片段

    在PostgreSQL中,jsonb有额外的操作符,如 @、@、?、?|、? 可以用来查询是否包含路径/值,以及顶层键值是否存在。 详细文章:PostgreSQL 操作json/jsonb 那么,如果我们不知道路径,只想要查询json/jsonb是否存在某个片段,那就跟我一起来学习吧! 思路:使用 ::text 将json/jsonb转换成为

    2024年02月15日
    浏览(37)
  • 【C语言初阶】带你轻松玩转所有常用操作符(1) ——算数操作符,移位操作符,位操作符

    君兮_的个人主页 勤时当勉励 岁月不待人 C/C++ 游戏开发 Hello,这里是君兮_,最近要准备期末复习了,可能更新的就不会那么频繁了,下个星期恢复正常更新。 今天给大家带来的是操作符详解,由于这部分的内容比较多,可能会分成几部分讲,今天带来的是第一部分的内容,废

    2024年02月11日
    浏览(46)
  • 初始C语言——详细讲解操作符以及操作符的易错点

     第一章 “C“浒传——初识C语言(更适合初学者体质哦!)  第二章 详细认识分支语句和循环语句以及他们的易错点   第三章 初阶C语言——特别详细地介绍函数  第四章 初始C语言——详细地讲解数组的内容以及易错点  第五章  初始C语言——详细讲解操作符以及操

    2024年02月13日
    浏览(57)
  • 【c语言操作符系列1】^(异或操作符)讲解和多种例题详解

    目录 一、^ 是什么(^称为异或) 二、^的规律(特点) 三、可利用^秒杀的常见例题(重点) 1、消失的数字  2、不一样的人生密码 3、交换两个数(不能创建中间变量) 4、找出只出现一个的两个数字 是一种操作符, 针对二进制异或而言的 ,两个数对应的二进制位相同,异或

    2024年02月16日
    浏览(69)
  • Js中.?和??语法(可选链操作符和双问号操作符)

    Tips:为啥起这么一个标题呢,因为我自己当时看代码,看到这个语法的时候就感觉有些遗忘,但是又不知道他叫做什么名字,所以只能直接搜索.?和??这样搜索,哈哈哈相信不少人可能跟我一样,不知道他叫做什么名字嘿嘿。 可选链 操作符( ?. )允许读取位于连接对象链深

    2024年01月21日
    浏览(46)
  • 【C语言】中的位操作符和移位操作符,原码反码补码以及进制之间的转换

    欢迎大家来到c语言知识小课堂,今天的知识点是操作符和进制 同样都是数字1111,不同进制下数字的大小不同,第二行代表的是其各位数字十进制下的大小,将各位数字的十进制大小相加即1111在这个进制下转化为十进制的大小,从图中我们可以看出来 进制的定义:从右往左

    2024年02月22日
    浏览(52)
  • ES6...扩展操作符骚操作

    复制和合并数组变得更为简洁。不需要使用 concat() 方法或 slice() 方法,一个 … 操作符已经足够: 将数组作为一个需要单独一个个传入参数的函数的参数 对象属性展开复制 如果存在相同的值 后者覆盖前者 合并对象 类似于object.assign 不过,扩展属性并不总能生成与 Object.as

    2024年02月14日
    浏览(40)
  • 前端JS实用操作符,一些骚操作✨

             目录 0、!!  双重逻辑非操作符  📚 1、?? 操作符 空值合并/空判断  ✅ 2、?. 可选链运算符🔍 3、??= 操作符 逻辑空值赋值运算符 💚 4、三元运算符 📗 5、~~ 操作符 双位运算符 🔨 6、与 ||或 短路运算符 🚂 7、| 0 取整 🚁 8、 1 判断奇偶数 🎨 9、_ 数值分割

    2024年02月14日
    浏览(47)
  • 【C】操作符详解

    今天给大家带来一篇关于C语言操作符的详细介绍,在C语言中操作符主要有以下几种: 1.算术操作符 2.移位操作符 3.位操作符 4.赋值操作符 5.单目操作符 6.关系操作符 7.逻辑操作符 8.逗号表达式 9.条件操作符 9.下标引用,函数调用和结构成员 接下来给大家详细介绍! + - * /

    2024年02月12日
    浏览(37)
  • 操作符(超详解)

    算数操作符 移位操作符 位操作符 赋值操作符 单目操作符 关系操作符 逻辑操作符 条件操作符 逗号操作符 除了 % 操作符之外 , 其他的几个操作符 可以作用于 整数 和 浮点数 。 对于 / 操作符如果两个操作数都为 整数 ,执行 整数除法 。而只要有 浮点数 执行的就是 浮点数

    2024年02月15日
    浏览(41)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包