2 Replies Latest reply on Sep 10, 2015 4:42 PM by Prashanthi Kairuppala

    convert a comma separated string to json array

    Prashanthi Kairuppala Novice

      Hello,

       

      I have created a virtual view where i concat two fields and get a single comma separated field. now i am trying to convert this string to a json array

       

      value of field i have is -> "one, two" . Datatype -> string

      i want to get it as [one, two]

       

      I am trying with the below query.

       

      SELECT

        JSONARRAY(V2.users_string.column_string) AS column_string

        FROM

        V2.users_string

       

      But i get the below error.

      The column 'column_string' with runtime type 'string' does not match the runtime type 'clob' from the query transformation

       

      If i convert the datatype the datatype of the column to clob, my odata url gives the below error

       

      TEIID30492 Expressions of type OBJECT, CLOB, BLOB, or XML cannot be used in SELECT DISTINCT, ORDER BY, GROUP BY, KEYS, or non-all set queries: [g0.column_string]

       

      how do i solve this

       

      Thanks in advance,

      Prashanthi.

        • 1. Re: convert a comma separated string to json array
          Steven Hawkins Master

          JSONARRAY converts one or more arguments into a clob that is the json for the resulting array, so JSONARRY('one', 'two') would give you what you expect - ["one","two"].

           

          SELECT

            JSONARRAY(V2.users_string.column_string) AS column_string

            FROM

            V2.users_string

           

          would give you ["one,two"]

           

          > The column 'column_string' with runtime type 'string' does not match the runtime type 'clob' from the query transformation

           

          The return type for JSONARRAY is clob.

           

          > If i convert the datatype the datatype of the column to clob, my odata url gives the below error

           

          Do you have a primary key on column_string or are using it in another transformation where you expect it be comparable/sortable?  By default clobs are not comparable.

           

          Generally what you would do is convert from clob to string (assuming that your json values are under 4000 characters) - cast(JSONARRAY(...) as string)

          • 2. Re: convert a comma separated string to json array
            Prashanthi Kairuppala Novice

            Thanks steven,

             

            On casting to string i am able to get the correct response.

             

            Prashanthi.