2

I have a table with column type as json.

select request_id,cast(job_info as varchar)  from requests_table where cast(job_id as varchar) like '%4665-b583-3b92218db8ce%';

Here job_info and job_id are the json type column.

It yield me data like this:

          request_id   |                 job_info
--------------------------------------+-------------------------------------------------------------------------------------------------------------------
 f373085d-0124a161b2d7 | ["test-cluster", "test-cluster-id-11111"]
(1 row)

I am expecting job_info data should get retrieved as two separate column.

f373085d-0124a161b2d7,test-cluster,test-cluster-id-11111

1 Answer 1

2

You can extract each array element using the->> operator:

select request_id,
       job_info ->> 0 as cluster_1, 
       job_info ->> 1 as cluster_2
from requests_table 
where cast(job_id as varchar) like '%4665-b583-3b92218db8ce%'
3
  • Thanks.. Perfect!. I was doing something like this. Thanks again Nov 19, 2020 at 13:08
  • what if my job_id has multiple values like job_info and I have to look specific value using where clause.. let me know if it has to be another question. Thanks Nov 19, 2020 at 13:39
  • 1
    yes, please ask a new question.
    – user1822
    Nov 19, 2020 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.