How to reference a PostGIS JSON field in GeoServer SLD

Question

I have a PostGIS table with a JSON field which I successfully published in GeoServer 2.16 The contents of the JSON field (named "display_properties") are an array and like this (some values have been omitted):

[{"name" : {"en" : "Level", "el" : ""}, "value" : {"en" : 1, "el" : 1}}, ... ]

I would like to make an SLD to symbolize the layer with an attribute-based rule from the contents of the JSON file. Is there a chance I might use something like this?

<?xml version="1.0" encoding="ISO-8859-1"?>
<StyledLayerDescriptor version="1.0.0"
                       xsi:schemaLocation="http://www.opengis.net/sld http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd"
                       xmlns="http://www.opengis.net/sld" xmlns:ogc="http://www.opengis.net/ogc"
                       xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <NamedLayer>
    <Name>test_JSON_field</Name>
    <UserStyle>
      <Title>A cyan line style</Title>
      <FeatureTypeStyle>
        <Rule>
          <Title>cyan line</Title>
          <ogc:Filter>
            <ogc:PropertyIsEqualTo>
              <ogc:PropertyName>display_properties[0]["value"]["en"]</ogc:PropertyName>
              <ogc:Literal>1</ogc:Literal>
            </ogc:PropertyIsEqualTo>
          </ogc:Filter>
          <LineSymbolizer>
            <Stroke>
              <CssParameter name="stroke">#0099cc</CssParameter>
            </Stroke>
          </LineSymbolizer>
        </Rule>
      </FeatureTypeStyle>
    </UserStyle>
  </NamedLayer>
</StyledLayerDescriptor>

P.S. There's an older similar question (GeoServer and PostgreSQL JSON column) but not about referencing a JSON field in SLD.

Answer 1

There are two possible ways to make this work:

1. upgrade the GeoTools Postgis code to handle JSON columns in a general way.
2. to write a custom function that could process the JSON column inside the SLD to return the required element as an expression the SLD parser could understand.

I suspect that the 2nd approach would still need some of the 1st one to read in the JSON as I suspect it is currently ignored by the driver code.