json - 如何用 JSON 表示 PostgreSQL 中的稀疏 RDBS 列

Question

假设我们在 rational db(表)中有用于测量特征的列:A、B、C、D、...、Z。它们每个都有 3 列:名称、值、错误。样本是行，一个样本的每个特征都有一个或零个测量值。 A 和 B 列的数据已填充 90%，但 C、D、...、Z 非常罕见 - 稀疏列(大约 10% 的单元格在每个单元格中均不包含空值)。

使用 JSON 在 PostgreSQL 中存储这些数据的最佳方式是什么？

我的变体(新表有两列:序列号和 JSON)

1. 将示例的 JSON 数组存储在一个单元格中(一个原始行匹配一个新行)。

2. 将示例的 JSON 数组拆分为多行(一行中有一个数组元素；因此，一个原始行与几个新行匹配)。

3. 使用有理数列:)

示例:2 个原始行提供这些 JSON 字符串:

----------row 1----------
[
    {
        "name" : "A",
        "value" : 3.300000,
        "err" : 1.200000,
    },
    {
        "name" : "B",
        "value" : 730.000000,
        "err" : 112.000000,
    },
    {
        "name" : "E",
        "value" : 22.600000,
        "err" : 4.700000,
    },
    {
        "name" : "H",
        "value" : 58.300000,
        "err" : 11.100000,
    }
]
----------row 2----------
[
    {
        "name" : "A",
        "value" : 2.100000,
        "err" : 1.400000,
    },
    {
        "name" : "J",
        "value" : 266.000000,
        "err" : 65.000000,
    },
    {
        "name" : "K",
        "value" : 14.700000,
        "err" : 3.800000,
    }
 ]

我应该使用哪个？

如果我有示例中提到的带有记录的文本文件(原始表的每一行的 JSON 数组)，如何将此数据集导入 PostgreSQL？

Answer 1

导入:

首先使用 COPY 以纯文本形式导入数据，以防它不是有效的 JSON 格式(这里就是这种情况)。您可以使用 pattern matching对于一些基本的清理，然后用一个简单的 type cast 解析它.如果文件不容易从数据库访问，您可以查看 psql \copy以及它的 PGAdmin wrapper

create table public.measurement_samples_raw (sample_row text);

/* /home/username/measurement_samples.json:
[{"name":"A","value":3.300000,"err":1.200000,},{"name":"B","value":730.000000,"err":112.000000,},{"name":"E","value":22.600000,"err":4.700000,},{"name":"H","value":58.300000,"err":11.100000,}]
[{"name":"A","value":2.100000,"err":1.400000,},{"name":"J","value":266.000000,"err":65.000000,},{"name":"K","value":14.700000,"err":3.800000,}] */
copy public.measurement_samples_raw (sample_row) 
    from '/home/your_username/measurement_samples.json';

update public.measurement_samples_raw set 
    sample_row = regexp_replace(
                    '{"top_key":'||sample_row||'}', --unnamed lists aren't supported, so adding a key and wrapping in {...}
                    ',\s*}', '}',--pattern and replacement to remove trailing commas
                    'g' --forces the function to replace all instances of the pattern
                    );

alter table public.measurement_samples_raw 
    add column sample_id serial,--so that each measurement sample has an ID
    add column sample_row_jsonb jsonb;
    
update public.measurement_samples_raw 
    set sample_row_jsonb=sample_row::jsonb;

存储:

您可以规范化结构并通过使用 json type functions and operators 提取来填充它

drop table if exists public.measurement_samples;
create table public.measurement_samples (
    id    serial,
    name  char(1),
    value numeric,
    err   numeric,
    constraint measurement_samples_pk primary key (id, name) --I assume you don't want >=2 values for characteristic 'A' in a single measurement row
);

insert into public.measurement_samples (id, name, value, err)
select  sample_id,
        (single_measurement_in_sample->>'name')::text,
        (single_measurement_in_sample->>'value')::numeric,
        (single_measurement_in_sample->>'err')::numeric
from (        
    select  sample_id,
            json_array_elements(
                sample_row::json->'top_key'
            ) as single_measurement_in_sample
    from public.measurement_samples_raw
    ) raw_input;

这给了你一个无缝的结构:

table public.measurement_samples;
-- id | name |   value    |    err
------+------+------------+------------
--  1 | A    |   3.300000 |   1.200000
--  1 | B    | 730.000000 | 112.000000
--  1 | E    |  22.600000 |   4.700000
--  1 | H    |  58.300000 |  11.100000
--  2 | A    |   2.100000 |   1.400000
--  2 | J    | 266.000000 |  65.000000
--  2 | K    |  14.700000 |   3.800000
--(7 rows)

您可以根据您的需要重新排列，而不会浪费空间，但会牺牲性能 - 除非您创建 View materialized :

create view public.v_measurement_samples as
select  id, 
        sum(value) filter (where name='A') as "A_value",
        sum(err) filter (where name='A') as "A_err",
        sum(value) filter (where name='B') as "B_value",
        sum(err) filter (where name='B') as "B_err",
        sum(value) filter (where name='C') as "C_value",
        sum(err) filter (where name='C') as "C_err",
        sum(value) filter (where name='D') as "D_value",
        sum(err) filter (where name='D') as "D_err",
        sum(value) filter (where name='E') as "E_value",
        sum(err) filter (where name='E') as "E_err"
from    public.measurement_samples
group by id
order by id;

--table public.v_measurement_samples;
-- id | A_value  |  A_err   |  B_value   |   B_err    | C_value | C_err | D_value | D_err |  E_value  |  E_err
------+----------+----------+------------+------------+---------+-------+---------+-------+-----------+----------
--  1 | 3.300000 | 1.200000 | 730.000000 | 112.000000 |         |       |         |       | 22.600000 | 4.700000
--  2 | 2.100000 | 1.400000 |            |            |         |       |         |       |           |
--(2 rows)

工作 dbfiddle示例。