No luck returning JSON array values

General
1

I’ve tried several different syntaxes to perform a where search on a JSON column containing array data. Given an “actions” column that looks like this:

[ { "pay": 100, "type": "offer", "driver": { "name": "blah", "objectId": "B4C1EF5B-7F8D-4520-BF2F-A83BFA621127" } }, ... ]

how can I return rows whose first array element has type = ‘offer’?

actions->'$.type[0]' = 'offer'

returns “no data”. I’ve tried to access other members of the array using the synax above, and using JsonSearch and JsonContains but this array just seems completely inaccessible, sometimes with bizarre errors: see https://backendlessappcontent.com/8149FCCE-07DA-F988-FFBB-6154872C9500/BD711666-4D18-42F1-8FFF-9E94491C03A2/files/web/error.png.

One more thing … not all of the actions column are of the same schema, so the ‘type’ member isn’t present in all rows - must all JSON column rows have the same members?

Thank you!

2

Hi Kelly,

I believe it would be this:

actions->'$[0].type' = 'offer'

Since the type property is in the first object (if that one is present), you need to use the following “construct”: $[0]

Regards,
Mark

3

I found the records I want by treating the JSON column like string data and searching for

actions like '%"type": "assign"%'

but I would still like to know how to perform a string search of a json array, and if that strange error shown in the pic is a result of something I am doing. Thx

4

Oh shoot, that does work now. Sorry to bother you. Given the error in the foto, I think my browser was in a “state”. After refreshing the window, I can’t reproduce that error now either. As always, thank you for the quick response.

1 Like