Reading all values as text from jsonb datatype in PostgreSQL

Question

I want to read all elements of a json to get the value as text but I am getting value with double quotes. Also help me how better i will read all the elements of nested json which have multiple array elements.

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;

What do you mean by *better reading all the elements of nested json which have multiple array*? [`select '{"a":"b"}'::jsonb->>'a';`](https://dbfiddle.uk/A9qBvpT9) doesn't give me double quotes. What client/IDE are you using? Are you sure the values you queried don't actually contain the double quotes (your attached example doesn't)? — Zegarek, May 10 '23 at 07:22
Thank you selvazi to format the code. Thank you zegarek for you reply. Actually in the above json all values are with double quotes but I have find work around to read the value as text. But unable to do for minApp, mgrType,mgrId . It all gives value with double quotes. Hope you understand the problem. — sss111ind, May 10 '23 at 07:47
Does this answer your question? [Postgres: How to convert a json string to text?](https://stackoverflow.com/questions/27215216/postgres-how-to-convert-a-json-string-to-text) — Bergi, May 10 '23 at 08:04

Zegarek · Answer 1 · 2023-05-10T08:28:30.250

The double quotes you're seeing are because the values you retrieved using jsonb_path_query() are of jsonb type. You can check using 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

To get the value without those, you could get the outer object using the function, then get the text value from it with ->> operator, then maybe even cast it using ::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;

Thank you Zegarek, it would solve my issue. Thanks to all. – sss111ind May 10 '23 at 09:15 — sss111ind, May 10 '23 at 09:15

Reading all values as text from jsonb datatype in PostgreSQL

1 Answers1