FeatureLayer Definition Expression

[SaurabhUpadhyaya]

Hi Friends,

I am trying to implement Definition Expression on Feature Layer.

In our application account and consultant are stored in db as json format {"Account": "Hospital", "Consultant": "Pediatrics"} datatype charcter varaying.

Now when i am trying to filter data on definition expression "Consultant": "Pediatrics" and it is not giving any result.

Can anyone help me

[UndralBatsukh]

It should be layer.definitionExpression = "Consultant = 'Pediatrics'"

[JoelBennett · Accepted solution]

If the JSON text is stored in string format like shown, I think you'd need a query more like the following (where "FIELD_NAME" is the name of the field being filtered):

FIELD_NAME LIKE '%"Consultant": "Pediatrics"%'

Setting the definitionExpression would then be something like:

layer.definitionExpression = "FIELD_NAME LIKE '%\"Consultant\": \"Pediatrics\"%'";

[SaurabhUpadhyaya]

Hi @JoelBennett 

Thanks for your response and it is working fine.

Now as i explained in previous question data is stored in json format where some values are integer/ double where i want to use < less than or > greater than operator in definition expression.

JSON Data :  {"Account": "Hospital", "Consultant": "Pediatrics", "Sales": 65.0, "Assets": 2.5}

definition expression : 

This is just an idea not exactly, want to add and Sales > 65.0 with existing definition expression ("Consultant": "Pediatrics" and  Sales > 65.0)

FIELD_NAME LIKE '%"Consultant": "Pediatrics"%' 

. 

[JoelBennett]

This is admittedly ugly, and there may be a better way, but in theory it should work:

FIELD_NAME LIKE '%"Consultant": "Pediatrics"%' AND TO_NUMBER(SUBSTR(FIELD_NAME, INSTR(FIELD_NAME, 1, '"Sales": ') + 9, INSTR(FIELD_NAME, 1, ', "Assets":') - (INSTR(FIELD_NAME, 1, '"Sales": ') + 9))) > 65

Some databases out there like Oracle provide native support for JSON fields, but I don't think ArcGIS necessarily supports that field type though.

[SaurabhUpadhyaya]

Its not working.

In my postgres db data stored in character varaying datatype and values are stored in json format.

Can you please check again,

Thanks

[SaurabhUpadhyaya]

I have followed the syntax provided on https://enterprise.arcgis.com/en/server/latest/administer/linux/about-standardized-queries.htm

[JoelBennett]

My background is primarily in Oracle, and I've never used Postgres.  If you're limited to standardized queries then you won't be able to do this with just a definitionExpression.

An alternative would be to query the service with the filters you can use, further filter the results on the client-side, and then add all matching features to a GraphicsLayer or client-side FeatureLayer.