Dữ liệu GIS Mẹo vặt Sói's Tutorials

Postgresql cast array text to array

Table of Content

Postgresql cast array text to array

Hôm nay mình có vấn đề thế này, trong PostgreSQL mình có lưu tọa độ dạng thế này

image

Mình muốn chuyển nó sang Geometry để dễ dàng phân tích, truy vấn không gian. Mình đã dùng hàm ST_GeomFromGeoJSON thấy ok. Tuy nhiên có vấn đề với hàm ST_GeomFromGeoJSON là nó không khai báo đây là loại geometry gì. Và khi mở lớp này bằng QGIS mình cũng không export được (nghi do ko khai báo dạng geometry)

image

Vì vậy mình muốn đọc vô [106.65168699999998,10.768864] như một mảng để lấy từng phần tử x, y. Sau đó make point với 2 tham số này. Tuy nhiên vọc hoài chưa có cách này cast cái này sang mảng trong PostgreSQL :')


https://dba.stackexchange.com/questions/54283/how-to-turn-json-array-into-postgres-array

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements per row in a LATERAL or correlated subquery. Then original order is preserved and we don't need ORDER BYGROUP BY or even a unique key in the outer query. See:

Replace 'json' with 'jsonb' for jsonb in all following SQL code.

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

Related:

ARRAY constructor in correlated subquery:

SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

Related:

Subtle differencenull elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE (in Postgres 9.6 or later!) to not stand in the way of parallelism. See:

Call:

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

db<>fiddle here

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

Original (outdated) SQL Fiddle.
db<>fiddle here.

Related:

Notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar JSON value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...

Cách khác

Tôi thấy cái này

https://stackoverflow.com/questions/47359288/convert-a-text-to-an-array-postgresql

Thế là có ý tưởng, chuyển chuỗi [106.65168699999998,10.768864] về {106.65168699999998,10.768864} theo đúng format array của postgresql

SELECT	id,
		((replace(replace((t1.toado::jsonb ->> 'coordinates')::text,'[','{'),']','}'))::numeric[])[1] AS x,
		((replace(replace((t1.toado::jsonb ->> 'coordinates')::text,'[','{'),']','}'))::numeric[])[2] AS y
FROM covid_f0 t1
WHERE "toado" != 'None'
LIMIT 5

Kết quả

image

Leave a Reply

Your email address will not be published. Required fields are marked *