topic Re: FeatureLayer Definition Expression in ArcGIS JavaScript Maps SDK Questions

FeatureLayer Definition Expression

SaurabhUpadhyaya — Tue, 07 May 2024 13:19:23 GMT

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

Re: FeatureLayer Definition Expression

UndralBatsukh — Tue, 07 May 2024 14:10:53 GMT

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

Re: FeatureLayer Definition Expression

JoelBennett — Tue, 07 May 2024 17:20:42 GMT

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\"%'";

Re: FeatureLayer Definition Expression

SaurabhUpadhyaya — Wed, 08 May 2024 10:25:33 GMT

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"%'

Re: FeatureLayer Definition Expression

JoelBennett — Wed, 08 May 2024 17:08:37 GMT

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.

Re: FeatureLayer Definition Expression

SaurabhUpadhyaya — Fri, 10 May 2024 13:15:05 GMT

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

Re: FeatureLayer Definition Expression

SaurabhUpadhyaya — Fri, 10 May 2024 13:20:01 GMT

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

Re: FeatureLayer Definition Expression

JoelBennett — Fri, 10 May 2024 16:53:01 GMT

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.