7 Replies Latest reply on Oct 30, 2015 1:47 PM by Steven Hawkins

    datatype for json array object

    Prashanthi Kairuppala Novice

      Hi,

       

      We have a column which is returning a json array like given below:

      [

      {"name":"john"},

      {"age":"20"}

      ]

       

      When the datatype of the field is string, the odata url is returning the value as given below:

      "[

      {\"name\":\"john\"},

      {\"age\":\"20\"}

      ]"

       

      What should be the datatype of the field so that it returns the json array without escape charatcers.

       

      Thanks in advance,

      Prashanthi.

        • 2. Re: datatype for json array object
          Steven Hawkins Master

          Ramesh that would produce EDM.Binary correct?

           

          Prashanthi, are you expecting the json to be inlined?

          • 3. Re: datatype for json array object
            Prashanthi Kairuppala Novice

            we do not expect the json to be displayed inlined.

            we just want it to be displayed without starting and ending double quotes and escape characters.

            e.g. [{"name":"john"},{"age":"22"}]

             

            Thanks,

            Prashanthi.

            • 4. Re: datatype for json array object
              Steven Hawkins Master

              This is appearing in a json response correct?  Can you provide an full example of what this should look like?  It seems like if this is not inlined, then it would need to appear as a literal value and must be escaped.

              • 5. Re: datatype for json array object
                Prashanthi Kairuppala Novice

                Hi Steven,

                Please find the below expected and returned values.


                Expected result from odata :

                {    "resposnes": [        {            "name": "john"        },        {            "age": "20"        }    ]}

                 

                But we are getting for string datatype :

                 

                {    "resposnes": "[        {            \"name\": \"john\"        },        {            \"age\": \"20\"        }    ]"}

                 

                value returned in Teiid designer when Preview data : [        {            "name": "john"        },        {            "age": "20"        }    ]

                 

                Thanks,

                Prashanthi.

                • 6. Re: datatype for json array object
                  Ramesh Reddy Master

                  Prashathi,

                   

                  You are defining a "string" type for JSON based data, and expecting it to be JSON when it is retrieved, which is not correct assumption. If you want the field to be unaltered, you can try the "byte[]"/clob as the data type on the table where you are are sending, then ODATA will return a byte array that you can convert into a string.

                   

                  otherwise, when you read your JSON string {"resposnes": [{"name": "john"}, {"age": "20"}]} in the Teiid, using the JSONTOXML function and XMLTABLE convert into a view/table columns, then you can get the response you need. Otherwise, the string is trying to preserve the quote (") that has been present in the payload.

                   

                  Ramesh..

                  • 7. Re: datatype for json array object
                    Steven Hawkins Master

                    For the value to appear directly as JSON in the response it would instead have to match the entity model.  So rather than a string column this data would have to come from an association or an array in your Teiid data model.  Otherwise as Ramesh says it will be some type of literal - either string or binary and it would be up to the consumer to know that the nested value is json.

                     

                    It does seem that you are looking for the value to be inlined, but we currently don't have the metadata/internal support for that.  We'd have to know the structure of the JSON value so that we could provide the proper OData entity model and have different output logic.  Ideally we'll get further along scenarios like that in Teiid 9.x