PostgreSQL 9.4-选择JSONB字段的动态子集

Question

假设我有一个带有一些JSONB列的表。JSON相当庞大，在大多数情况下，我只需要通过字段名称检索其内容的5%（只是为了提高性能）。所需字段的集合因情况而异，但它仍然占整个JSON数据的5%左右。

我知道如何对硬编码字段集执行此操作。问题是，是否可以使用外部提供的字段集。这需要作为存储函数来实现。

以下是带有"硬编码字段集"的示例：

CREATE TABLE test_data (
  json_data jsonb NOT NULL
);
INSERT INTO test_data(json_data)
VALUES ('{"row_id": 1, "f1": 1, "f2": 2, "f3": 3, "f4": 4}'),
       ('{"row_id": 2, "f1": 1, "f2": 2, "f3": 3, "f4": 4}');

CREATE OR REPLACE FUNCTION get_important_data(IN data_set varchar = 'row_id,f1,f2')
  RETURNS table (data json)
  LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY 
  SELECT (SELECT row_to_json(data_row) FROM (
        -- this needs to be dynamic
        select json_data->>'row_id' as "row_id",
               json_data->>'f1' as "f1",
               json_data->>'f2' as "f2"
      ) data_row ) as data
  FROM test_data;
END $$;
SELECT get_important_data(/* use default data set*/);
SELECT get_important_data('row_id,f2,f4');

Answer 1

虽然我认为（但我不确定）您可以在没有任何动态SQL和一些繁琐的连接和类似枢轴的查询的情况下逃脱惩罚，但我认为这里最直接、最可能执行的方法是在函数中进行一点动态查询。

这将是我对你的解决方案的快速理解。注意显式转换为text，以重新创建->>运算符的效果：

CREATE OR REPLACE FUNCTION get_important_data(data_set text DEFAULT 'row_id,f1,f2') RETURNS TABLE (
    data    json
) AS $$
BEGIN
    RETURN QUERY EXECUTE format('SELECT row_to_json(j.*) FROM test_data, LATERAL jsonb_to_record(json_data) AS j(%s);', 
        (SELECT array_to_string(array_agg(trim(field)), ' text, ') || ' text'
            FROM unnest(string_to_array(data_set, ',')) AS field)
    );
END;
$$ LANGUAGE plpgsql;

但在我看来，由于您最终返回的是仍为json格式的json文件的一个子集，因此最好保持原样。我更喜欢下面的版本，它也应该运行得更快：

CREATE OR REPLACE FUNCTION get_important_data(data_set text DEFAULT 'row_id,f1,f2') RETURNS TABLE (
    data    json
) AS $$
DECLARE
    rec_fields  record;
    arr_fields  text[];
BEGIN
    FOR rec_fields IN 
        SELECT trim(unnest(string_to_array(data_set, ','))) AS field 
    LOOP
        arr_fields :=  arr_fields || format('json_data->%1$L AS %1$I', rec_fields.field);
    END LOOP;
    RETURN QUERY EXECUTE format('SELECT row_to_json(r) FROM (SELECT %s FROM test_data) AS r', array_to_string(arr_fields, ','));
END;
$$ LANGUAGE plpgsql;

最后一点是放弃text输入参数，而选择json输入参数（'["row_id", "f1", "f2"]'），但我将留给您。