ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

How Do You Parse a json string contained in a program variable?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How Do You Parse a json string contained in a program variable?

    I just wanted to make sure I'm not overlooking a simpler solution for parsing a json string that exists in a program variable.


    Code:
    dcl-s jsonString varchar(32765) inz;
    Can jsonString be parsed by some db2 function? Or do I have to get that string into a table column defined as a clob then use json_table() to parse the column?



  • #2
    If you want to use SQL you'd use JSON_TABLE. No need to write the string to a table/column, from RPG you'd simply do this:

    Code:
    exec SQL select (whatever columns)
               into (whatever variable)
               from JSON_TABLE( :jsonString, 'lax $'
                                COLUMNS( ...whatever they are... )
                              )
    The values of the columns, etc, would depend on the content of the document... but I hope you get the idea, you just feed your string in as a variable to JSON_TABLE.

    You could also use other methods such as DATA-INTO or calling routines like the YAJL routines, too... There are lots of options available for this type of work.

    Comment


    • TheZenbudda
      TheZenbudda commented
      Editing a comment
      Thanks for your response. I must be doing "something" wrong then. Let me make a few more attempts before posting some generic code.

    • AS400_GKool
      AS400_GKool commented
      Editing a comment
      Hello Scott, Thank you for showing this sample code. I tried this in my SQLRPGLE - however I am receiving a Row not found for SELECT error. My code is very simple -
      H dftactgrp(*no) actgrp(*new)

      D jsonstring S 100A varying
      D name S 10A
      D school S 10A
      *
      D Cmd PR ExtPgm('QCMDEXC')
      D 200A Options(*Varsize) Const
      D 15P 5 Const
      *
      D Command S 200A
      *
      /free

      Command = 'CHGJOB CCSID(37)';
      Cmd(Command: %len(%Trim(Command)));

      jsonstring = '{"name":"abc"}';
      Exec Sql
      select * into :name from
      json_table(:jsonstring, '$'
      columns(name varchar(10) path '$.name'));
      Dsply name;
      *inlr = *on;

      /end-free


      Could you please suggest what could be wrong with this code and what could be the cause of this error? Thank you. Gauri

  • #3
    I meant to write back earlier in the week. The problem is the incoming json did not have the key name in quotes and the key name was the word "type" (minus the quotes of course). Since a web service was creating the json, I had assumed it would be properly formatted but it wasn't.

    Comment


    • #4
      Could you please show an extract of your JSON document? If the key name is not embedded in double quotes it is not a valid JSON document.

      Birgitta

      Comment


      • #5
        Birgitta,

        Thanks for your inquiry/interest. I've modified the sample below, but only the name of the key and value of the key/value pair.

        Code:
        {
            type:"transaction", 
            key:"ab12cd45", 
            reference:"123456789", 
            valid:"N", 
            result:"Approved", 
            code:"ABC123", 
            yoruref:"123456789x"
        }
        jsonlinit doesn't like it either, so I'm not sure why a 3rd party is formatting json like this.

        Comment


        • #6
          That's valid JavaScript, but it isn't valid JSON.

          Comment


          • #7
            Originally posted by Scott Klement View Post
            That's valid JavaScript, but it isn't valid JSON.
            Thanks for confirming. I think the 3rd party vendor is producing "a report" of sorts, instead of sending us the actual json string.

            Comment

            Working...
            X