Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.9, 10.2, 10.3
-
None
-
Windows x64
Description
MariaDB [appdev4db]> drop database if exists appdev4db;
|
Query OK, 1 row affected (0.022 sec)
|
|
|
MariaDB [(none)]> create database appdev4db;
|
Query OK, 1 row affected (0.002 sec)
|
|
|
MariaDB [(none)]> use appdev4db;
|
Database changed
|
MariaDB [appdev4db]> create table T1 (C1 INT, C2 CHAR(4), C3 GEOMETRY);
|
Query OK, 0 rows affected (0.033 sec)
|
|
|
MariaDB [appdev4db]> insert into T1 values (1,'AAAA',ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [74.6466372, 16.8241727]}'));
|
Query OK, 1 row affected (0.005 sec)
|
|
|
MariaDB [appdev4db]> select * from T1;
|
+------+------+---------------------------+
|
| C1 | C2 | C3 |
|
+------+------+---------------------------+
|
| 1 | AAAA | !ÿ■Çb⌐R@è┴h√ⁿ╥0@ |
|
+------+------+---------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [appdev4db]> select JSON_ARRAY(C1,C2,C3) from T1;
|
+---------------------------------------------------------------------------------------+
|
| JSON_ARRAY(C1,C2,C3) |
|
+---------------------------------------------------------------------------------------+
|
| [1, "AAAA", "\u0000\u0000\u0000\u0000\u0001\u0001\u0000\u0000\u0000!ÿ■Çb⌐R@è┴h√ⁿ╥0@"] |
|
+---------------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [appdev4db]> select JSON_ARRAY(ST_ASGEOJSON(C3)) from T1;
|
+----------------------------------------------------------------------+
|
| JSON_ARRAY(ST_ASGEOJSON(C3)) |
|
+----------------------------------------------------------------------+
|
| ["{\"type\": \"Point\", \"coordinates\": [74.6466372, 16.8241727]}"] |
|
+----------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [appdev4db]> select JSON_ARRAY(JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
|
+--------------------------------------------------------------+
|
| JSON_ARRAY(JSON_QUERY(ST_ASGEOJSON(C3),'$')) |
|
+--------------------------------------------------------------+
|
| [{"type": "Point", "coordinates": [74.6466372, 16.8241727]}] |
|
+--------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [appdev4db]> select JSON_ARRAY(C1,JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
|
+-----------------------------------------------------------------+
|
| JSON_ARRAY(C1,JSON_QUERY(ST_ASGEOJSON(C3),'$')) |
|
+-----------------------------------------------------------------+
|
| [1, {"type": "Point", "coordinates": [74.6466372, 16.8241727]}] |
|
+-----------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [appdev4db]> select JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
|
+---------------------------------------------------------------------------------+
|
| JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$')) |
|
+---------------------------------------------------------------------------------+
|
| [1, "AAAA", "{\"type\": \"Point\", \"coordinates\": [74.6466372, 16.8241727]}"] |
|
+---------------------------------------------------------------------------------+
|
1 row in set (0.001 sec)
|
There a number of releated problems exposed here..
The first query returns the geometry objects s an escaped string. While this it technically not incorrect I would have expected it to return the GeoJSON repsentation of the column automatically, or at the very least the repesentation output by ST_ASTEXT (If the conversion to GeoJSON is lossey).
The second query returns the location object as GEOJSON, but the output of ST_ASGEOJSON is treated as a string, rathe than as a JSON object by JSON_ARRAY. Again this is not useful.
The thrid query applies HACK to attempt to force JSON_ARRAY to treat the output of ST_ASGEOJSON as JSON. It works when selecting one column, and when selecting 2 columns but when the JSON is the third column selected it reverts to interpreitng the output of JSON_QUERY as string rather than as JSON.