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.

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

          Did you try Binary (byte[])?

          • 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