Query JSON Data

Testing out the JSON columns and have no real experience with JSON, but can’t seem to accomplish a (seemingly) simple task.
A sample of my data looks like this:

[
{"itemNO": "A1234",
"status": "Available"},
{"itemNO": "B1234",
"status": "Available"},
{"itemNO": "C1234",
"status": "Sold"}
]

I want to retrieve the status of an item based on the item number. So for example, the status for "C1234. Using JSONata, the expression “itemNO” gets me an array of all item numbers, but I can’t seem to figure out how to just get one value based on the item number. Any guidance would be appreciated.

Hola!

By “accident”, you have an object array with 3 items. :wink:

Here we explained your case and another similar: Create json from whole table - #3 by gvalero

To sum up, what you need is:

.[2]."status"

Saludos!

$[itemNO="C1234"].status

image

Another test.

JSONata is awesome.

1 Like

Ah, we are talking about the new Query JSON column… WTF!! :rofl:

I still try to understand the main advantages, but in the meantime this syntax works too:

image

$[2].status

In addition,

$[].status   ==>  $[itemNO].status

image

For more reference:

$[2].status returns the status of the 3rd element in the array (index 2).

$[itemNO].status returns an array format of all status. But you can use a join function to get a comma-delimited list instead, which might be easier down the line for further transformation.

image

5 Likes

This is so great! Works perfectly.

2 Likes

Super cool, we can create crazy things like this:

Shouldn’t this plugin be called Query JSONata or have some “JSONata” reference to make it different from the current Transform JSON plugin? :woozy_face:

Gracias Thinh!

3 Likes

Yeah, I would love if we can get a JSONata reference in there :wink:

Playing around with this a little more, it’s a disappointing limitation that the JSON source cannot be a URL. Or am I missing something. I know the existing Fetch JSON column that uses JQ can, but not, seemingly, this newer JSON column(s).

Yeah currently it’s set up like that. I use the alpha version API call column (limited access) in tandem with this.

1 Like