Ballparks by James Fee

ballparks2.sql

/*
---------------------------------
ballparks2.geojson from James Fee
---------------------------------

Note that there's attribute "Teams", which has a value of a JSON array of JSON objects.
QGIS 3 interprets this as string. 
Turning this string into an array still is an array of JSON Objects.
So, this has to be treated first as a JSON object.

In QGIS 3 following expression functions can be applied:

1. Get Team name (text) of first JSON array object:
map_get(from_json("Teams")[0], 'Name')

2. Get all Team names as array:
array_foreach(from_json("Teams"), map_get(@element, 'Name')) 

3. Get Team names array as string, delimited with ' / ':
array_to_string( array_foreach( from_json("Teams"), map_get(@element, 'Name') ), ' / ')

The last example can e.g. be used as an expression functions for a label.

*/


--
-- FUNCTION jsonb_path_query_text(...)
-- JSONB Path helper function.
--
-- DROP FUNCTION public.jsonb_path_query_text(jsonb, jsonpath, jsonb, boolean);
create or replace function jsonb_path_query_text(
    target jsonb,
    path jsonpath,
    vars jsonb default '{}'::jsonb,
    silent boolean default false)
returns text
as $$
    select jsonb_path_query(target, path, vars, silent) #>> '{}';
$$ language sql immutable strict parallel safe;


--
-- Ballpark(s) from James Fee
--
-- Originally contained one feature Ballpark "Salt River Fields at Talking Stick".
-- Extended by another feature Ballpark "Red Sox Fields Test".
-- Treated as plain GeoJSON document in PostGIS using PostgreSQL's JSON path implementation.
--

with geojson_features as (
    select * from jsonb_path_query((select * from (values (
'{
    "name": "ballparks",
    "type": "FeatureCollection",
    "features": [
    {
        "type": "Feature",
        "geometry": {
            "type": "Point",
            "coordinates": [-111.88552, 33.54523]
        },
        "properties": {
            "Ballpark": "Salt River Fields at Talking Stick",
            "Teams": [
                { "Name": "Arizona Diamondbacks", "Class": "Spring Training", "League": "Cactus" },
                { "Name": "Colorado Rockies", "Class": "Spring Training", "League": "Cactus" },
                { "Name": "Arizona League Diamondbacks", "Class": "Rookie", "League": "Arizona League" },
                { "Name": "Salt River Rafters", "Class": "Off Season", "League": "Arizona Fall League" },
                { "Name": "Scottsdale Scorpions", "Class": "Off Season", "League": "Arizona Fall League" }
            ]
        }
    },
    {
        "type": "Feature",
        "geometry": {
            "type": "Point",
            "coordinates": [-110.88552, 33.54523]
        },
        "properties": {
            "Ballpark": "Red Sox Fields Test",
            "Teams": [
                { "Name": "Red Sox", "Class": "Spring Training", "League": "MLB" }            ]
        }
    }
]
}'::jsonb)) as tmp), '$.features[*]') as geojson(feature)
)
select 
    row_number() over () as "team_id",
    team_name, 
    team_class, 
    team_league,
    ballpark_name, 
    geom
from (
    select
        jsonb_path_query(feature, '$.properties.Teams[*]')->>'Name' as team_name,
        jsonb_path_query(feature, '$.properties.Teams[*]')->>'Class' as team_class,
        jsonb_path_query(feature, '$.properties.Teams[*]')->>'League' as team_league,
        jsonb_path_query_text(feature, '$.properties.Ballpark') as ballpark_name,
        st_setsrid(st_makepoint(
            jsonb_path_query_text(feature, '$.geometry.coordinates[0]')::decimal(10,7),
            jsonb_path_query_text(feature, '$.geometry.coordinates[1]')::decimal(10,7)
        ),4326)::geometry(point, 4326) as geom
    from geojson_features
) as ballparks;
/*
 team_id |          team_name          |   team_class    |     team_league     |           ballpark_name            |       geom
---------+-----------------------------+-----------------+---------------------+------------------------------------+-------------------
       1 | Arizona Diamondbacks        | Spring Training | Cactus              | Salt River Fields at Talking Stick | 0101000020E61000007
       2 | Colorado Rockies            | Spring Training | Cactus              | Salt River Fields at Talking Stick | 0101000020E61000007
       3 | Arizona League Diamondbacks | Rookie          | Arizona League      | Salt River Fields at Talking Stick | 0101000020E61000007
       4 | Salt River Rafters          | Off Season      | Arizona Fall League | Salt River Fields at Talking Stick | 0101000020E61000007
       5 | Scottsdale Scorpions        | Off Season      | Arizona Fall League | Salt River Fields at Talking Stick | 0101000020E61000007
       6 | Red Sox                     | Spring Training | MLB                 | Red Sox Fields Test                | 0101000020E6100000D
(6 rows)
*/