Assets REST 2.0 json query

RT Developers
1

Hi there!

i’m struggeling with retrieving data using rest 2.0. Apparently i don’t know how to build a query which contains a custom field.

With simple queries, using basic fields everything works fine - i get the data i want
ex. assets?query=[{“field” : “Catalog”, “value” : “3”},{“field” : “id”, “value” : “183”}]

A piece of json structure for CustomFields:
“CustomFields” : [
{
“name” : “Customer”,
“values” : [],
“type” : “customfield”,
“id” : “35”,
“_url” : “/REST/2.0/customfield/35”
},
{
“name” : “Next Renewal”,
“values” : [
“2022-02-02”
],
“type” : “customfield”,
“id” : “67”,
“_url” : “/REST/2.0/customfield/67”
}]

So i thought that a valid query should look like this:

domain.name/REST/2.0/assets?query=[{“field” : “Catalog”, “value” : “3”},{“field” : “CustomFields”, “value” : [{“field”: “name”, “value”:“Next Renewal”}, {“field”: “values”, “value”: “2022-02-02”}], “entry_aggregator”: “AND”}]

i have also tried:

domain.name/REST/2.0/assets?query=[{“field” : “Catalog”, “value” : “3”},{“field” : “CustomFields”, “value” : [{“field”: “Next Renewal”, “value”:“2022-02-02”}], “entry_aggregator”: “AND”}]

and this:

domain.name/REST/2.0/assets?query=[{“field” : “Catalog”, “value” : “3”},{“field”: “CustomFields”, “value”: [{“name” : “Next Renewal”, “values” : [“2022-02-02”]}], “entry_aggregator”: “AND”}]

but this does not work. The query (in both cases) returns all values valid for the first argument (Catalog=3), but it doesn’t include the filter Next Renewal=“2022-02-02” .

What am I missing here? Any ideas? Thanks in advance for your help.

Best Regards

2

Does this syntax work?

curl --location --request POST 'http://MyRT/REST/2.0/assets' \
--header 'Content-Type: application/json' \
--data-raw '[
    {"field"  : "CustomField.{Test CF}", "value": "one"}
]'

Also since RT 5 has asset SQL you can do something like this too and get your query from the asset query builder:

curl --location -g --request GET 'http://MyRT/REST/2.0/assets?query=Catalog = '\''General assets'\'' AND '\''CF.{Test CF}'\'' LIKE '\''one'\''' \
--data-raw ''
3

Unfortunately, it doesn’t work :frowning:

4

Hi,

tried the
[{“field” : “CustomField.{Test CF}”, “value”: “one”}]
[{“field” : “CustomField.{Test CF}”, “values”: “one”}]

and also
[{“field” : “CustomField.{Test CF}”, “value”: “one”}]
[{“field” : “CustomField.{Test CF}”, “values”: “one”}]

as the key in JSON dump is in plural
“CustomFields” : [
{
“name” : “CFname1”,
“values” : [
“one”
],
}

Tried also the SQL querry form , but it doesn’t work.
Assets search does not accept ticketSQL according to the docs on [bestpractical.com]
Maybe there is some kind of workaroud

JSON form works, only on basic attributes,
rt5/REST/2.0/assets?query=[{“field”:“Catalog”,“value”:“3”}]

Tried the SQLform
REST/2.0/assets?Query=Catalog+%3D+%27MyAssets%27+AND+%27CF.%7BCFName1%7D%27+%3D+%272023-08-02%27

5

This works for me on RT 5.0.1:

curl --location -g --request GET "http://MyRT/REST/2.0/assets?query=Catalog='General%20assets'%20AND%20'CF.{Test}'%20LIKE%20'Test'"

Which version of RT are you on?

6

We are on 5.0.0 version

7

further tests performed…

We’ve replaced REST2 with the one from 5.0.1 version. While running:

curl --location -g --request GET "http://MyRT/REST/2.0/assets?query=Catalog='General%20assets'%20AND%20'CF.{Test}'%20LIKE%20'Test'"

we get:
[error]: malformed JSON string, neither array, object, number, string or atom, at character offset 0 (before “Catalog='CatName…”) at /opt/rt5/sbin/…/lib/RT/REST2/Resource/Collection/QueryByJSON.pm line 78. (/opt/rt5/sbin/…/lib/RT/REST2/Middleware/Log.pm:62)

8

Ah it looks like the change needed is in the new 5.0.2 release:

  • Support FromSQL queries for Assets and Transactions in REST 2

https://docs.bestpractical.com/release-notes/rt/5.0.2beta1

9

seems that it works! :smile:
Thanks!