mysql - 如何在 MySQL 8 中通过 int 而不是通过名称从嵌套 JSON 中获取

Question

所以我目前正在使用 MySQL 的 JSON 字段来存储一些数据。

因此“报告”表如下所示:

id | stock_id | type             | doc                          |
1  | 5        | Income_Statement | https://pastebin.com/bj1hdK0S|

pastebin是json字段的内容

我想要做的是从 JSON 中每年 (2018-12-31) 下的第一个对象获取一个数字 (ebit)，然后使用它进行 WHERE 查询，以便它仅返回 ebit > 50000000 的情况例如。问题是每年下的日期不标准(即一个可能是 2018-12-31，另一个可能是 2018-12-15)。所以本质上我想要一种使用整数索引而不是对象的实际名称来获取数据的方法，例如yearly.[0].ebit。

我该如何在 MySQL 中执行此操作？或者，如果在 MySQL 中不可能，那么在 PostgeSQL 或 Mongo 中是否可能？如果是这样，你能给我举个例子吗？大多数数据都适合 MySQL，只是这个表有一个 JSON 列，这就是我开始使用 MySQL 的原因。

所以 StackOverflow 不会让我在没有代码的情况下链接到 Pastebin，所以这里有一些随机代码:

if(dog == "poodle") {
    print "test"
}

Answer 1

我不知道 MySQL 或 MongoDB，但这里有一个 PostgreSQL JSONB 类型的简单版本:

SELECT (doc->'yearly'-> max(years) -> 'ebit')::numeric AS ebit
FROM reports, jsonb_object_keys(doc->'yearly') AS years
GROUP BY reports.doc;

...使用简单的测试数据:

WITH reports(doc) AS (
    SELECT '{"yearly":{"2018-12-31":{"ebit":123},"2017-12-31":{"ebit":1.23}}}'::jsonb
)
SELECT (doc->'yearly'-> max(years) -> 'ebit')::numeric AS ebit
FROM reports, jsonb_object_keys(doc->'yearly') AS years
GROUP BY reports.doc;

...给出:

 ebit 
------
  123
(1 row)

所以我基本上选择了 "yearly" 下的最新条目不知道实际值，但假设关键日期格式将允许排序顺序(在这种情况下，它似乎符合 ISO-8601)。

使用数据类型 JSON而不是JSONB会保留对象键顺序，但在 PostgreSQL 中效率不高，并且在这里也没有帮助。

如果您想要，则仅选择那些 reports条目有最新的 ebit大于某个值，只需将其打包到子选择或CTE中。我通常更喜欢 CTE，因为它们更容易阅读，所以我们开始:

WITH
    reports (id, doc) AS (
        VALUES
        (1, '{"yearly":{"2018-12-31":{"ebit":123},"2017-12-31":{"ebit":1.23}}}'::jsonb),
        (2, '{"yearly":{"2018-12-23":{"ebit":50},"2017-12-22":{"ebit":"1200.00"}}}'::jsonb)
    ),
    r_ebit (id, ebit) AS (
        SELECT reports.id, (reports.doc->'yearly'-> max(years) -> 'ebit')::numeric AS ebit
        FROM reports, jsonb_object_keys(doc->'yearly') AS years
        GROUP BY reports.id, reports.doc
    )
SELECT id, ebit
FROM r_ebit
WHERE ebit > 100;

但是，正如您已经看到的，无法使用此策略过滤原始行。预处理步骤在这里是有意义的，这样 JSON 格式实际上是过滤器友好的。

附录

为了增加为第 n 个完整会计年度选择值的可能性，我们需要借助窗口函数，并且还需要减少结果集，以便每个实际值仅返回一行组(在演示案例中:reports.id):

WITH reports(id, doc) AS (VALUES
    (1, '{"yearly":{"2018-12-31":{"ebit":123},"2017-12-31":{"ebit":1.23},"2016-12-31":{"ebit":"23.42"}}}'::jsonb),
    (2, '{"yearly":{"2018-12-23":{"ebit":50},"2017-12-22":{"ebit":"1200.00"}}}'::jsonb)
)
SELECT DISTINCT ON (1) reports.id, (reports.doc->'yearly'-> (lead(years, 0) over (partition by reports.doc order by years desc nulls last)) ->>'ebit')::numeric AS ebit
FROM reports, jsonb_object_keys(doc->'yearly') AS years
GROUP BY 1, reports.doc, years.years ORDER BY 1;

...的行为与使用 max 完全相同之前的聚合函数。增加 lead(years, <offset>) 范围内的偏移参数函数 all 将选择向后的第 n 年(因为窗口分区的降序)。

DISTINCT ON (1)子句的神奇之处在于，它可以将结果减少为每个不同列值一行(第一列 = reports.id )。这就是为什么 NULLS LAST在窗口内非常重要OVER条款。

以下是不同偏移量的结果(我为第一个 id 添加了第三个历史条目，但没有为第二个条目添加了第三个历史条目，以显示它如何处理缺失的条目):

N = 0:

 id | ebit 
----+------
  1 |  123
  2 |   50

N = 1

 id |  ebit   
----+---------
  1 |    1.23
  2 | 1200.00

N = 2

 id | ebit  
----+-------
  1 | 23.42
  2 |

...这意味着缺少条目只会导致 NULL值。