How to transform pl sql Json value into a string-CodePudding

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

CodePudding user response：

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)
...

CodePudding user response：

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