json - 将 JSON 文件的嵌套子文件导入到 Postgresql

Question

我正在尝试从 JSON 文件中导入少量数据。 JSON 文件是嵌套的，我想导入子值。 JSO 结构是这样的

{
    "type": "FeatureCollection",
    "properties": {
        "zoom": 14,
        "x": 12302,
        "y": 7075
    },
    "features": [
        {
            "type": "FeatureCollection",
            "properties": {
                "layer": "poi",
                "version": 3,
                "extent": 4096
            },
            "features": [
                {
                    "type": "Feature",
                    "id": 4356,
                    "properties": {
                        "fid": "eg-34678h765",
                        "name": "Brooklyn Children's Museum"
                    },
                    "geometry": {
                        "type": "Point",
                        "coordinates": [
                            -73.944030,
                            40.674427
                        ]
                    }
                }
            ]
        }
    ]
}

我想获取以下子值(就像我用 JS 调用它一样)

features[0].features[i].id
features[0].features[i].properties.fid
features[0].features[i].properties.name
features[0].features[i].geometry.coordinates[0]
features[0].features[i].geometry.coordinates[1]

进入 myTable 标题为 id、fid、name、longitude 的列， 纬度

我想出了一个解决方案，但它只通过 psql 插入父值，如 type、properties、features

copy temp_json from 'E:\myJson.json';

insert into myTable ("type", "properties", "features") 

select values->>'type' as type,
       values->>'properties' as properties,
       values->>'features' as features
from   (
           select json_array_elements(replace(values,'\','\\')::json) as values 
           from   temp_json
       ) a;

其中 features 作为 JSONB 插入。

如何从 JSON 文件中获取我想要的字段并插入到我的表的目标列中？

Answer 1

试试这个

select j2->>'id' as id,
       j2->'properties'->>'fid'  as fid,
       j2->'properties'->>'name' as name,
       MAX( CASE WHEN l.k = 1 THEN l.cord end ) as longitude,
       MAX( CASE WHEN l.k = 2 THEN l.cord end ) as latitude
       from temp_json 
    cross join json_array_elements(values->'features') as j1
    cross join json_array_elements(j1->'features') as j2
    cross join json_array_elements_text(j2->'geometry'->'coordinates')
                                 with ordinality l(cord,k)
                                 GROUP BY 1,2,3

DEMO