Query JSON - quotation marks

Ask for Help
1

How do I get the query JSON column to return a string value without quotation marks using JSONata?

1 Like
2

I think I know what you mean ā€¦

are your annoying quotation marks at the beginning and end of the received JSON data, right?

I suffered this yesterday :woozy_face:

Can you show some screenshots to verify if my workaround is useful for you?

Saludos!

1 Like
3

We talked to @Brett about this yesterday. If I hear something from him, Iā€™ll let you know.

2 Likes
4 
{"id":"string","resource":"payment","mode":"test","amount":{"value":"600.00","currency":"QAR"},"description":"Jamm Factory :: Monday - 14:00 :: Drummer","method":"creditcard","redirectUrl":"URLstring","webhookUrl":null,"status":"open","organizationId":"string","sequenceType":"oneoff","metadata":{},"createdAt":"2023-08-24T21:34:43.001Z","expiresAt":"2023-08-24T21:49:42.990Z","_links":{"self":{"href":"URLstring","type":"application/hal+json"},"checkout":{"href":"URLstring","type":"text/html"},"dashboard":{"href":"URLstring","type":"text/html"}}}

I need $._links.checkout.href but it is returning ā€œURLstringā€. I can remove them with an additional template column, but itā€™s not really a solution with bigger objects with many multiple values needing to be extracted.

2 Likes
5

Not sure what to do for this! If this is a bug glide team will fix it! In the meantime for one column solution you can use the JavaScript column!

image

image
image592Ɨ859 36.9 KB

JS Code :

const jsonData = JSON.parse(p1);
const checkoutHref = jsonData._links.checkout.href;
return checkoutHref;

Thank you

6

I donā€™t think itā€™s a bug, I think itā€™s default JSONata behaviour. There is probably a simple way to force JSONata to return an unquoted string, but Iā€™ve been searching the docs and havenā€™t found it yet :man_shrugging:

Iā€™ll be watching this thread to see if somebody provides a simple answer.

2 Likes
7

This is so strange, I have this setup on one of my projects and it was working fine without the quotes, now itā€™s suddenly including the quotes.

Is there a way to extract the text within the quotes? Iā€™ve tried using ā€˜Extract Matching Textā€™ but havenā€™t had any luck.

8

Yes, my experience was similar, I could have sworn on the first day the quotation marks didnā€™t appear.

The easiest solution I found for my purposes was to use a template column and replace " with nothing.

Obviously a terrible hack and only worked for me because the string itself didnā€™t contain that character. Iā€™m not quite savvy enough to implement to JavaScript solution above.

Iā€™m hoping a solution will appear that doesnā€™t require additional columns :pray::crossed_fingers:

1 Like
9

I can confirm this. Not sure what changed on their side.

10

yes I think something was changed from some JSONata library

I used to receive the data like:

Aug 18, 2023, 10:13:31 PM|#83a7b|Olguita|555000|0412-555000|olga@dsados.com|Qta Olga, maracay edo. aragua|- (1) RISOTTO ALLā€™ARAGOSTA: US$9.60|US$9.6|N/A|

but now I have the same data wrapped in quotes :woozy_face:

" Aug 18, 2023, 10:13:31 PM|#83a7b|Olguita|555000|0412-555000|olga@dsados.com|Qta Olga, maracay edo. aragua|- (1) RISOTTO ALLā€™ARAGOSTA: US$9.60|US$9.6|N/A|"

My workaround to remove these quotes (at the beginning and at the end) was using a JS code (@Adam_Crossley):

image

let lon= p1.length
return p1.substring(1, lon-1)

I hope it helps meanwhile

Saludos!

2 Likes