Found a solution by using this demo dashboard.
In my JSON API query fields, I created a new field called compliance which is has two possible values green or red based on the values of value and min and max.
The JSONata query looks something like this:
$map(*, function($v, $i, $a) {
($v.min != null and $v.value >= $v.min) or
($v.max != null and $v.value <= $v.max)
? 'green'
: 'red'
})
Then use the transformation Config from query results following the same logic as the demo dashboard:
Last step:
Add a color background override to the value field:
