How to transform pl sql Json value into a string

Question

Have a project for work & my SQL skills are improving, but I still struggle with basic stuff every now and then. I need to get 'fundingCode' to show up as a string rather than a number as it is now.

(i.e. currently the outcome is '"fundingCode": 100001' & I need it to show up as '"fundingCode": "100001"

cursor json_index_data (c_org_code VARCHAR2) is 
    select json_object(
            '_dtoName' VALUE 'AeSFndOrganization',
            'companyId' VALUE oa.location_desc,
            'deptId' VALUE OA.DEPT,
            'ocCode' VALUE OA.ORGANIZATION_LEVEL_6,
             nvl('seq', 1) VALUE imi.seq, 
            'fundingCode' VALUE (I.ACCOUNT_INDEX)
    FORMAT JSON)
    as json_row_value

Just gave it a shot, throwing an error unfortunately. Really appreciate the suggestion though. — kevinvi8
– kevinvi8, Commented Dec 20, 2022 at 17:49

Marc · Accepted Answer · 2022-12-20 18:24:17Z

1

The way you would typically do a select statement resulting in JSON would be like this:

select json_object(
        key '_dtoName' is 'AeSFndOrganization',
        key 'companyId' is oa.location_desc,
        key 'deptId' is OA.DEPT,
        key 'ocCode' is OA.ORGANIZATION_LEVEL_6,
        key 'seq' is imi.seq, 
        key 'fundingCode' is to_char(I.ACCOUNT_INDEX)
      ) as YOUR_JSON_ALIAS
   from YOUR_TABLENAME;

So you define your key on the left and your value (with is) on the right. I don't think that would act any differently in a cursor if you just put cursor json_index_data is in front of it.

If you need to convert a value, use the typical conversion functions like to_char(the_value) or to_number(the_string), etc. That's what I did in the sample query above for:

...
key 'fundingCode' is to_char(I.ACCOUNT_INDEX)
...

answered Dec 20, 2022 at 18:24

Marc

11.6k2 gold badges40 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kevinvi8 · Accepted Answer · 2022-12-21 16:20:12Z

0

So a coworker managed to give me a pretty good explanation. I had previously tried to use concatenation, but I was using bad syntax. Here is how it was finally accomplished.

cursor json_index_data (c_org_code VARCHAR2) is 
    select json_object(
            'multitenantId' VALUE '1',
            '_dtoName' VALUE 'AeSFndOrganization',
            'companyId' VALUE oa.campus_desc,
            'deptId' VALUE OA.DEPT,
            'ocCode' VALUE OA.ORGANIZATION_LEVEL_6,
            nvl('seq', 1) VALUE imi.seq,                 
            'fundingCode' VALUE '"' || I.ACCOUNT_INDEX || '"'
    FORMAT JSON)
    as json_row_value

answered Dec 21, 2022 at 16:20

kevinvi8

3312 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to transform pl sql Json value into a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related