在PostgreSQL中从jsonb数据类型阅读所有值作为文本

ltqd579y 于 2023-05-17 发布在 PostgreSQL

关注(0)|答案(2)|浏览(161)

我想读取JSON的所有元素以获得文本形式的值，但我获得的值带有双引号。也请帮助我如何更好地读取嵌套的JSON的所有元素，这些元素有多个数组元素。

create table jtest (id integer,doc jsonb);

insert into jtest values
(1, '{
   "empdet":{
      "isMgr":false,
      "deptno":"102",
      "selectedDept":{
         "deptno":"102",
         "empName":"MILLER SALESMAN" },
      "selectedMgrs":[
         {  "id":"1",
            "list":[
               {  "mgrName":"KING",
                  "mgrRole":"KING PRESIDENT" },
               {  "mgrName":"SCOTT",
                  "mgrRole":"SCOTT MGR" }
            ],
            "minApp":"1"
         }
      ]
   },
   "jobIds":[ 770 ],
   "appMgrs":[
      {  "mgrId":"KING",
         "mgrType":"U"
      }
   ],
   "deptLoc":"NEW YORK"
}');

insert into jtest values
(2, '{
   "empdet":{
      "isMgr":false,
      "deptno":"101",
      "selectedDept":{
         "deptno":"101",
         "empName":"SMITH SALESMAN" },
      "selectedMgrs":[
         {  "id":"2",
            "list":[
               {  "mgrName":"KING",
                  "mgrRole":"KING PRESIDENT" },
               {  "mgrName":"BLAKE",
                  "mgrRole":"BLAKE MGR" }
            ],
            "minApp":"1"
         }
      ]
   },
   "jobIds":[ 775 ],
   "appMgrs":[
      {  "mgrId":"KING",
         "mgrType":"U"
      }
   ],
   "deptLoc":"NEW YORK"
}');

select  id,
        doc-> 'empdet'->'selectedDept'->>'empName' empName,
        doc-> 'empdet'->>'deptno' deptno,
        jsonb_path_query_first(doc, '$.empdet.selectedMgrs.id' ) id,
        jsonb_path_query(doc, '$.empdet.selectedMgrs.list[*]' )->>'mgrRole' mgrRole,
        jsonb_path_query(doc, '$.empdet.selectedMgrs.list[*]')->>'mgrName' mgrName,
        jsonb_path_query_first(doc, '$.empdet.selectedMgrs.minApp' ) minApp,
        jsonb_path_query_first(doc, '$.appMgrs.mgrId') mgrId,
        jsonb_path_query_first(doc, '$.appMgrs.mgrType' ) mgrType,
        doc->>'deptLoc' deptLoc,
        jsonb_path_query_first(doc, '$.jobIds[*]' ) jobIds
from jtest;

postgresql

来源：https://stackoverflow.com/questions/76215796/reading-all-values-as-text-from-jsonb-datatype-in-postgresql

2条答案

按热度按时间

rkttyhzu1#

您看到的双引号是因为您使用jsonb_path_query()检索的值是jsonb类型。您可以使用pg_typeof()进行检查：demo

select  doc-> 'empdet'->'selectedDept'->>'empName' empName,
        jsonb_path_query_first(doc, '$.empdet.selectedMgrs.id' ) id,
        pg_typeof(jsonb_path_query_first(doc,'$.empdet.selectedMgrs.id')) id_type
from jtest;
--     empname     | id  | id_type
-------------------+-----+---------
-- MILLER SALESMAN | "1" | jsonb
-- SMITH SALESMAN  | "2" | jsonb

要获取没有这些的值，你可以使用函数获取外部对象，然后使用->>运算符从它获取文本值，然后甚至可以使用::int转换它：demo

select 
      id
     ,doc-> 'empdet'->'selectedDept'->>'empName' empName
     ,doc-> 'empdet'->>'deptno' deptno
     ,(jsonb_path_query_first(doc, '$.empdet.selectedMgrs[*]')->>'id')::int id
     ,jsonb_path_query(doc, '$.empdet.selectedMgrs.list[*]' )->>'mgrRole' mgrRole
     ,jsonb_path_query(doc, '$.empdet.selectedMgrs.list[*]')->>'mgrName' mgrName
     ,(jsonb_path_query_first(doc, '$.empdet.selectedMgrs[*]')->>'minApp') minApp
     ,jsonb_path_query_first(doc, '$.appMgrs[*]' )->>'mgrId' mgrId
     ,jsonb_path_query_first(doc, '$.appMgrs[*]' )->>'mgrType' mgrType
     ,doc->>'deptLoc' deptLoc
     ,jsonb_path_query_first(doc, '$.jobIds[*]' ) jobIds
from jtest;

赞(0）回复(0）举报 2023-05-17

xjreopfe2#

我想读取JSON的所有元素以获得文本形式的值，但我获得的值带有双引号。
由于jsonb_path_query_first返回jsonb，我们需要首先将结果转换为文本，然后使用REPLACE删除引号：

select id,
replace(jsonb_path_query_first(doc, '$.empdet.selectedMgrs.minApp' )::text,'"','') 
from jtest;

也请帮助我如何更好地读取嵌套的JSON的所有元素，这些元素有多个数组元素。
要访问一个数组，例如selectedMgrs，你可以使用jsonb_array_elements：

select t.id, elm.value
from jtest t
CROSS JOIN jsonb_array_elements(t.doc->'empdet'->'selectedMgrs') elm

要访问数组的数组，请尝试以下操作：

select t.id, elm.value, subElm.*, subElm->>'mgrName'
from jtest t
CROSS JOIN jsonb_array_elements(t.doc->'empdet'->'selectedMgrs') elm
CROSS JOIN jsonb_array_elements(elm->'list') subElm

要获取文本形式的JSON元素，可以使用运算符->>
Demo here

赞(0）回复(0）举报 2023-05-17

我来回答

在PostgreSQL中从jsonb数据类型阅读所有值作为文本

2条答案

相关问题

热门标签

最新问答