Question 161756 in PostgreSQL Question posted in
The official documentation can be found here.

Can text be read from jsonb datatype in PostgreSQL?

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;
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    I want to read all elements of a json to get the value as text but I
    am getting value with double quotes.

    Since jsonb_path_query_first return jsonb, we need first convert the result to text then remove quotes using REPLACE :

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

    Also help me how better i will read all the elements of nested json
    which have multiple array elements.

    To access an array for exemple selectedMgrs you can use jsonb_array_elements :

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

    To access array of arrays try this :

    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

    To get JSON element as text you can use the operator ->>

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search