ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

JSON_TABLE parsing

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

  • JSON_TABLE parsing

    Hello,
    I would like to process the below json file via Sql command :
    Select * from JSON_TABLE(get_clob_from_file('/tmp/currencies.json'),
    '$'
    Columns(​.......

    ..........



    I'm stuck as the currency code (i.e. AED , AFN .,.. USD) has no column name and changes at every instance


    Thanks
    Attached Files

  • #2
    Hi HMiller,

    this is not a valid json, try to put it into a json validator online, it is invalid.

    Bye

    Comment


    • #3
      Hi paolinosal,
      Sorry, you are right.
      I've just added file as attachment.
      I validated it online.
      Bye


      Comment


      • #4
        JSON_TABLE's 2nd parameter is a JSON path used to determine what is considered a "row" in the table. Since JSON paths can have wildcards, you could simply select '$.*' as the row -- this means each currency in the document you provided would be a separate row.

        I don't know how you'd get the currency code itself... but, to get the fields for each currency, you'd just define columns. So if what you need is the currency name, demonym and symbol, you could do this:

        Code:
        select  t.name, t.demonym, t.symbol
        from
          JSON_TABLE(  the-json-document,
                    'lax $.*' COLUMNS(
                        name VARCHAR(30) PATH '$.name',
                        demonym VARCHAR(20) PATH '$.demonym',
                        symbol CHAR(1) PATH '$.symbol'
                    )
            ) as t

        Comment


        • #5
          Hi Scott,
          thank you for you explanation.
          Yes, I was trying to get the currency code as well. I will manually amend json file at this point by adding "currency code" field.

          Thanks

          Comment

          Working...
          X