如何按键从字段中提取jsonb列

Question

我正在使用postgresql和postgis插件。

我已经按照此方案存储了数据：table_id是主键，属性为jsonb，geom为geometry(GeometryZ,4326)；

如果我进行此请求：select table_id, properties, geom from nametable，则会返回所有信息，其中table_id为整数，properties为jsonb，geom为geometry(GeometryZ,4326)。

我想要一个查询，其中属性返回table_id，列的列表，其中列名为值的键，其值和几何图形。例如，如果属性具有名称和密度，则返回如下响应：table_id | name | density | geom |，但我不知道属性的名称，因此我认为应该在该查询内部进行查询以获取键的名称。

到目前为止，我最接近实现它的方法是使用以下查询：select jsonb_object_keys(properties) as key from nametable; 谢谢

编辑：

首先，我已将一个字段存储为jsonb在我的postgressql数据库中，因此我想将该jsonb提取到列中。但是已将包含不同属性的不同表存储在jsonb列中。

所以，我的想法是获得一个查询，其中选择table_id、属性（在多个列中提取）和geom。

1 - 通过这个，我可以获得键的名称：select jsonb_object_keys(properties) as key from nametable group by key; 2 - 使用键在列中获取每个键的所有值。

3 - 返回一个查询，在我调用它时，返回nametable中的table_id、从jsonb中提取的属性列和geom；

我的问题是我不知道如何使用子查询生成该查询。

- Javier

---

请提供以下内容：1）一些样本数据；2）您期望的输出结果；3）告诉我们您的查询出了什么问题。 - Jim Jones

好的，我将编辑问题以更好地解释自己。 - Javier

示例数据最好使用格式化文本呈现。请参考此处了解一些如何创建漂亮的表格的提示。 - user330315

Answer 1

您尝试过使用CTE吗？

WITH j AS (
  SELECT 
    table_id, 
    properties->>'name' AS name,
    properties->>'density' AS density,
    geom 
  FROM t)
SELECT * FROM j WHERE j.name = 'Alabama'; 

- Jim Jones

---

这很好，但我不知道jsonb列的键是什么..在这种情况下是名称和密度，但在其他情况下将是不同的键..所以我想动态获取键并像您发布的那样进行查询。 - Javier

Answer 2

感谢所有帮助我的人。

以下是与瓷砖服务器一起使用的查询代码。

我使用这个查询来获取键的数组：

const sql = SELECT ARRAY_AGG(f) as keys FROM (SELECT jsonb_object_keys(properties) f FROM ${options.layerName} group by f) u;

稍后，一个名为generateSQL的函数将被用于创建查询，以将每个属性作为列获取。

/** CONSTANTS **/
const TILE_SIZE = 256;
const PROJECTION_STRING = '+init=epsg:3857';

/** LIBRARIES **/
var zlib = require('zlib');
var express = require('express');
var mapnik = require('mapnik');
var Promise = require('promise');
var SphericalMercator = require('sphericalmercator');

const { pool } = require('../postgressql/config');

var mercator = new SphericalMercator({
    size: TILE_SIZE
});

mapnik.register_default_input_plugins();

var app = express();

app.get('/:namelayer/:z/:x/:y.pbf', (req, res, next) => {
    var options = {
        x: parseInt(req.params.x),
        y: parseInt(req.params.y),
        z: parseInt(req.params.z),
        layerName: req.params.namelayer
    };

   const sql = `SELECT ARRAY_AGG(f) as keys FROM (SELECT jsonb_object_keys(properties) f FROM ${options.layerName} group by f) u`;
    try {
        pool.query(sql, (error, results) => {
            if (error) {
                return res.status(500).json({
                    ok: false,
                    message: error
                });
            }

            const keys = (results && results.rows && results.rows.length > 0 && results.rows[0].keys && results.rows[0].keys.length >0) ? results.rows[0].keys.slice() : [];
            const sql = generateSQL(options, keys);

            makeVectorTile(options, sql).then( (vectorTile) => {
                zlib.deflate(vectorTile, (err, data) => {
                    if (err) {
                        return res.status(500).send(err.message);
                    }

                    res.setHeader('Content-Encoding', 'deflate');
                    res.setHeader('Content-Type', 'application/x-protobuf');
                    res.setHeader('Access-Control-Allow-Origin', '*');
                    return res.send(data);
                });
            });
        });
    } catch (e) {
        res.status(404).send({
            error: e.toString(),
        });
    }
});

function generateSQL(options, keys) {

    if (keys.length === 0) {
        return `select table_id, geom from ${options.layerName}`;
    } else {
        let sql = "";
        keys.forEach( key => {
            sql = sql + `(properties->>'${key}') as ${key},`;
        });
        sql = `select table_id, ${sql} geom from ${options.layerName}`
        return sql;
    }
};

function makeVectorTile(options, sql) {

    var extent = mercator.bbox(options.x, options.y, options.z, false, '3857');
    var map = new mapnik.Map(TILE_SIZE, TILE_SIZE, PROJECTION_STRING);
    map.extent = extent;

    var layer = new mapnik.Layer(options.layerName);
    layer.datasource = new mapnik.Datasource({
        type: process.env.DB_TYPE,
        dbname: process.env.DB_DATABASE,
        // table: options.layerName,
        table: `(${sql}) as tile`,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD
    });

    layer.styles = ['default'];
    map.add_layer(layer);

    return new Promise( (resolve, reject) => {
        var vtile = new mapnik.VectorTile(parseInt(options.z), parseInt(options.x), parseInt(options.y));
        map.render(vtile, function (err, vtile) {
            if (err) {
                return reject(err);
            }
            console.log(`${vtile.getData().length} KB`);
            resolve(vtile.getData());
        });
    });
};

module.exports = app;

- Javier