Topic
  • 3 replies
  • Latest Post - ‏2015-01-26T20:05:33Z by Taylor.Osmun (IBM)
lin-zhao
lin-zhao
28 Posts

Pinned topic Is there a way to do CONCAT function in 7.2.2?

‏2015-01-26T05:30:04Z |

In 7.2.3 and 7.2.4, I can write query with CONCAT. 7.2.2 doesn't seem to support it. However, my need for this function comes from the bad csv format returned from the REST API.

If payload is included in the query, and I request the search results in application/csv, the field is not quoted, and there are commas in the payload. This breaks the csv format. With 7.2.3 and 7.2.4, I can do "CONCAT('"', utf8(payload), '"')" to  add a pair of quotes and make the csv format valid. Since CONCAT is not in 7.2.2, I can not use this solution.

Is there a way to do something similar to CONCAT in 7.2.2? If not is there a way to get the result in the correct CSV format (e. g. add quotes to string fields in the result)?

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts
    ACCEPTED ANSWER

    Re: Is there a way to do CONCAT function in 7.2.2?

    ‏2015-01-26T17:28:40Z  

    Hi lin-zhao,

    Unfortunately, CONCAT is not available in 7.2.2. Improvements have been made since 7.2.3 to allow for a lot more functionality out of the ariel APIs. Have you considered using a different MIME type? application/json will properly wrap the content in double quotes. It is the only guaranteed way of fetching the data without conflict. Simply using CONCAT to wrap quotes, would require special processing to ignore quoted text instead the quotes.

    i.e. "this is "my" payload"

    Also, I do not believe that the 'utf8' function is required for 7.2.2; the functionality has changed in 7.2.3 which requires 'utf8' to see the actual value of the payload.

    - Taylor

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Is there a way to do CONCAT function in 7.2.2?

    ‏2015-01-26T17:28:40Z  

    Hi lin-zhao,

    Unfortunately, CONCAT is not available in 7.2.2. Improvements have been made since 7.2.3 to allow for a lot more functionality out of the ariel APIs. Have you considered using a different MIME type? application/json will properly wrap the content in double quotes. It is the only guaranteed way of fetching the data without conflict. Simply using CONCAT to wrap quotes, would require special processing to ignore quoted text instead the quotes.

    i.e. "this is "my" payload"

    Also, I do not believe that the 'utf8' function is required for 7.2.2; the functionality has changed in 7.2.3 which requires 'utf8' to see the actual value of the payload.

    - Taylor

  • lin-zhao
    lin-zhao
    28 Posts

    Re: Is there a way to do CONCAT function in 7.2.2?

    ‏2015-01-26T19:42:57Z  

    Hi lin-zhao,

    Unfortunately, CONCAT is not available in 7.2.2. Improvements have been made since 7.2.3 to allow for a lot more functionality out of the ariel APIs. Have you considered using a different MIME type? application/json will properly wrap the content in double quotes. It is the only guaranteed way of fetching the data without conflict. Simply using CONCAT to wrap quotes, would require special processing to ignore quoted text instead the quotes.

    i.e. "this is "my" payload"

    Also, I do not believe that the 'utf8' function is required for 7.2.2; the functionality has changed in 7.2.3 which requires 'utf8' to see the actual value of the payload.

    - Taylor

    I've tried application/json but sometimes the payload contains special characters (e. g. \t) that are not json-escaped, and the response body cannot be parsed by a json API. I haven't seen a payload we process that contain quotes yet.

     

     

  • Taylor.Osmun (IBM)
    Taylor.Osmun (IBM)
    55 Posts

    Re: Is there a way to do CONCAT function in 7.2.2?

    ‏2015-01-26T20:05:33Z  
    • lin-zhao
    • ‏2015-01-26T19:42:57Z

    I've tried application/json but sometimes the payload contains special characters (e. g. \t) that are not json-escaped, and the response body cannot be parsed by a json API. I haven't seen a payload we process that contain quotes yet.

     

     

    Hi lin-zhao,

    Thanks for trying it out. There is a known issue with JSON escaping with this particular endpoint. It is on the roadmap and will hopefully be fixed in a future release.

    Perhaps you could make your payload the last column in your application/csv result? If it was the last column, you could match the desired number of columns exactly.

    i.e.

    echo "1.1.1.1, 2.2.2.2, this is my, payload" | sed 's/\([^,]*\)\s*,\s*\([^,]*\)\s*,\s*\(.*\)/\1\n\2\n\3/g'
    1.1.1.1
    2.2.2.2
    this is my, payload

    - Taylor