ST_AsMVT returns broken tiles when column name and JSONB key are the same

[emreisikligil]

ST_AsMVT can encode row columns as feature attributes as well as keys and values in a JSONB column in the row data. It cannot handle the case where JSONB object contains a key with the name of a column properly. Returned MVT is formatted properly but some feature attributes are missing or shifted (values assigned to wrong keys).

The following query can be used to reproduce the issue. Please note that field1 exists as a column and a key in the JSONB column.

WITH my_data AS (
    SELECT ST_AsMVTGeom(
        ST_GeomFromText('POLYGON ((0 0, 10 0, 10 5, 0 -5, 0 0))'),
        ST_MakeBox2D(ST_Point(0, 0), ST_Point(4096, 4096)),
        4096, 0, false
    ) AS geometry, 'ffffffffffffffffffff' AS id, 'value1' AS field1, 
    '{"field1":"value1", "field2":"value2", "field3":"value3", "field4":"value4", "field5":"value5", "_field6":"value6", "_field7":"_value7", "_field8":"_value8"}'::JSONB
) SELECT ST_AsMVT(q,'my_layer',4096,'geometry') AS data
FROM (SELECT * FROM my_data) AS q

This query encodes feature attributes as follows.

VectorTileFeature {
  properties:{
     id: 'ffffffffffffffffffff',
     field1: 'value1',
     undefined: undefined,
     field2: 'value2',
     field4: 'value4',
     field5: 'value5',
     _field6: 'value6',
     _field8: '_value8' 
  },
  ...
  _keys: [ 
     'id',
     'field1',
     'field2',
     'field3',
     'field4',
     'field5',
     '_field6',
     '_field7',
     '_field8' 
  ],
  _values: [
     'ffffffffffffffffffff',
     'value1',
     'value2',
     'value3',
     'value4',
     'value5',
     'value6',
     '_value7',
     '_value8' 
  ] 
}

As it can be seen from the result, although all keys and values exist in the resulting data, attributes are not encoded properly (_field7 and field3 missing). I also came across where attributes are shifted (wrong values are assigned to keys) but could not reproduce the issue with this query.

In my opinion, either JSONB or row columns should have the higher precedence and overwrite the other. And, this information should be documented in the related docs.

Version:

POSTGIS="3.1.1 0" [EXTENSION] PGSQL="120" GEOS="3.8.1-CAPI-1.13.3" PROJ="7.1.1" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY

[Algunenano]

Looks like we aren't controlling that a key doesn't get added twice, which can only happen when you use JSON / JSONB types.

The spec is clear in saying that this is forbidden:

Feature attributes are encoded as pairs of integers in the tag field of a feature. The first integer in each pair represents the zero-based index of the key in the keys set of the layer to which the feature belongs. The second integer in each pair represents the zero-based index of the value in the values set of the layer to which the feature belongs. Every key index MUST be unique within that feature such that no other attribute pair within that feature has the same key index. A feature MUST have an even number of tag fields. A feature tag field MUST NOT contain a key index or value index greater than or equal to the number of elements in the layer's keys or values set, respectively.

I think that the simplest way to address this would be to keep only the first key-value pair and ignore any extra appearances of that key in that feature. If we went any other way we would need to remove the previously added value (only if it's unique) and that would be a pain.

Note that this is likely to also happen if you have multiple JSONB columns with the same key, so it's not an issue of just prefering columns of jsonb values.

[robe]

Any plans to fix this in next day or 2 - if not push somewhere

[Algunenano]

Not me. It's already tagged to an old release since it's a bug, where should it be pushed?

[pramsey]

Just as a note, because this issue has a workaround and very small surface of users who are affected by it, it's not gotten attention for multiple release cycles. If a patch is submitted, as always, we'll be happy to review.