Comments in SQL file for GeoServer layer

[Ushcode]

Problem

When a SQL query is specified as a filepath in the geoServerSettings.virtualTable.sql node of a data subset in the uploader, adding a comment can cause an issue. This appears to be due to the whole SQL query being flattened to one line, as such comments are still interpreted but anything that comes after them is commented out.

N.B. editing the SQL in the GeoServer gui allows for carriage returns to be added again so it does not appear to be necessary to flatten the SQL.

Example

Here is some code and the resultant GeoServer error.

In the config file, a data subset is:

{
            "type": "Vector",
            "skip": false,
            "schema": "public",
            "table": "ng_towers",
            "subdirectory": "vector/towers",
            "ogr2ogrOptions": {
                "layerCreationOptions": {
                    "GEOMETRY_NAME": "wkb_geometry"
                },
                "sridOut": "EPSG:27700"
            },
            "geoServerSettings": {
                "virtualTable": {
                    "name": "ng_towers",
                    "sql": "@/inputs/config/sql/virtualTables/towers_geoserver.sql",  // here is where the problem will come from
                    "geometry": {
                        "name": "wkb_geometry",
                        "type": "Point",
                        "srid": 27700
                    }
                },
                "defaultStyle": "twa:poles-towers"
            }
        }

the relevant SQL file:

SELECT
    "wkb_geometry",
    "srcName" as name,
    CONCAT(
        'http://www.theworldavatar.com/ontology/ontopower/OntoPower.owl#ng_poles_towers/',
        "ogc_fid"
    ) as iri
    -- ng_line_styling.icon_size,
    -- ng_line_styling.pylon_icon AS icon
FROM
    ng_towers
    -- ng_line_styling
-- WHERE
--     ng_towers.voltage = ng_line_styling.voltage_levelgft

The result is that GeoServer will not find the columns as it parses the FROM clause as commented out.

[Ushcode]

Uploaded GeoServer layer after removing comments to bypass issue

[Ushcode]

The above edit is accepted in the UI and doesn't break the layer

[sm453]

NB Strictly speaking, JSON does not support comments. Indeed some codes that process JSON break if one attempts to put comments in. For that reason, it is best to avoid that altogether...

[Ushcode]

Do you mean something like

"geoServerSettings": {
                "virtualTable": {
                    "name": "xxx",
                    "sql": "select yyy from zzz --  comment"
                 }
}

would cause a problem?

Or a comment within the json itself like a .jsonl one?

[sm453]

A comment within a string in a json file would of course not cause problems for the json itself, but

{
  "sql": "@/inputs/config/sql/virtualTables/towers_geoserver.sql",  // this is a problem.
}

[Ushcode]

Ah yes of course. That comment is just there for the example in this issue, not in any real config files