Query JSON Data

Ask for Help
1

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.

2

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!

3

$[itemNO="C1234"].status

image

Another test.

image
image1052×702 41.3 KB

JSONata is awesome.

1 Like
4

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

1 Like
5

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
6

This is so great! Works perfectly.

2 Likes
7

Super cool, we can create crazy things like this:

image
image765×322 12.2 KB

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
8

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

9

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).

10

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

1 Like