Question 110720 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How to transform pl sql Json value into a string

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

2

Answers


  1. Chosen as BEST ANSWER
    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

  2. 0

    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)
...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search