select place from records where id = 2 とした場合、最初の{"lat": 34.7325, "lng": 135.4986}と、最後の {"lat": 34.7344, "lng": 135.5035}だけを取り出すSQL文を書いて下さい。ただしplaceはjsonb形式です。
moove=# select place from records where id = 2; place ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [{"lat": 34.7325, "lng": 135.4986}, {"lat": 34.7326, "lng": 135.4989}, {"lat": 34.7326, "lng": 135.4989}, {"lat": 34.7327, "lng": 135.4995}, {"lat": 34.7327, "lng": 135.5}, {"lat": 34.7328, "lng": 135.5002}, {"lat": 34.733, "lng": 135.5006}, {"lat": 34.7331, "lng": 135.501}, {"lat": 34.7334, "lng": 135.5015}, {"lat": 34.7336, "lng": 135.5018}, {"lat": 34.7336, "lng": 135.5018}, {"lat": 34.7336, "lng": 135.5019}, {"lat": 34.7337, "lng": 135.5021}, {"lat": 34.734, "lng": 135.5024}, {"lat": 34.7343, "lng": 135.5026}, {"lat": 34.7346, "lng": 135.5024}, {"lat": 34.7347, "lng": 135.5024}, {"lat": 34.7346, "lng": 135.5024}, {"lat": 34.7345, "lng": 135.5025}, {"lat": 34.7343, "lng": 135.5027}, {"lat": 34.7343, "lng": 135.5029}, {"lat": 34.7342, "lng": 135.5031}, {"lat": 34.7344, "lng": 135.5035}, {"lat": 34.7344, "lng": 135.5035}, {"lat": 34.7344, "lng": 135.5035}] (1 row) で登場する、最初の{"lat": 34.7325, "lng": 135.4986}と、最後の {"lat": 34.7344, "lng": 135.5035}だけを取り出すSQL文を書いて下さい
ただし、placeは、以下の通り、jsonb形式です。
moove=# \d records;
Table "public.records"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('records_id_seq'::regclass)
user_id | integer | | |
distance | double precision | | |
place | jsonb | | |
speed | jsonb | | |
prediction_type | integer | | |
start_at | timestamp without time zone | | |
end_at | timestamp without time zone | | |
Indexes:
"records_pkey" PRIMARY KEY, btree (id)
moove=# \d records;
Table "public.records"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('records_id_seq'::regclass)
user_id | integer | | |
distance | double precision | | |
place | jsonb | | |
speed | jsonb | | |
prediction_type | integer | | |
start_at | timestamp without time zone | | |
end_at | timestamp without time zone | | |
Indexes:
"records_pkey" PRIMARY KEY, btree (id)
WITH places AS (
SELECT jsonb_array_elements(place) AS place_elements
FROM records
WHERE id = 2
)
SELECT
(SELECT place_elements FROM places LIMIT 1) AS first_place,
(SELECT place_elements FROM places ORDER BY jsonb_array_length(place) DESC LIMIT 1) AS last_place
FROM records
WHERE id = 2;出力結果
first_place | last_place
-----------------------------------+-----------------------------------
{"lat": 34.7325, "lng": 135.4986} | {"lat": 34.7325, "lng": 135.4986}
(1 row)