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

    datatype for json array object

    prashanthi1
    prashanthi1 Oct 29, 2015 9:50 AM

      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.

        • 1. Re: datatype for json array object
          rareddy
          rareddy Oct 29, 2015 2:50 PM (in response to prashanthi1)

          Did you try Binary (byte[])?

            • Actions
          • 2. Re: datatype for json array object
            shawkins
            shawkins Oct 29, 2015 3:10 PM (in response to rareddy)

            Ramesh that would produce EDM.Binary correct?

             

            Prashanthi, are you expecting the json to be inlined?

              • Actions
            • 3. Re: datatype for json array object
              prashanthi1
              prashanthi1 Oct 30, 2015 5:35 AM (in response to shawkins)

              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.

                • Actions
              • 4. Re: datatype for json array object
                shawkins
                shawkins Oct 30, 2015 7:05 AM (in response to prashanthi1)

                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.

                  • Actions
                • 5. Re: datatype for json array object
                  prashanthi1
                  prashanthi1 Oct 30, 2015 8:12 AM (in response to shawkins)

                  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.

                    • Actions
                  • 6. Re: datatype for json array object
                    rareddy
                    rareddy Oct 30, 2015 10:28 AM (in response to prashanthi1)

                    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..

                      • Actions
                    • 7. Re: datatype for json array object
                      shawkins
                      shawkins Oct 30, 2015 1:47 PM (in response to rareddy)

                      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

                        • Actions