时间戳

首页 > 解决方案 > 在postgres的json中计算唯一的嵌套键/值？

sql - 在postgres的json中计算唯一的嵌套键/值？

问题描述

如何计算postgres中json中唯一嵌套键/值？

每条user记录都有一个名为的 json 列hobby_years，其结构如下所示，但因用户而异。

用户的示例hobby_years值：

{
    "soccer": {
        "2006": 1,
        "2007": 1
    },
    "skiing": {},
    "basketball": {
        "2006": 1,
        "2016": 1,
        "2017": 1,
        "2018": 1
    },
    "painting": {
        "2008": 1,
        "2009": 1,
        "2014": 1,
        "2015": 1,
        "2016": 1
    }
}

我想知道一个或多个特定爱好中存在多少独特的年份。即：足球和篮球 - 应该返回5。

或者，如果可以使此活动更容易，我可以选择如下构造数据

{
    "soccer": [2006, 2007],
    "skiing": [],
    "basketball": [2006, 2016, 2017, 2018],
    "painting": [2008, 2009, 2014, 2015, 2016]
}

这可能吗？

标签： sqljsonpostgresql

解决方案

像这样的东西？

SELECT
        name, -- or whatever field you want to use to identify the user
        (SELECT COUNT(year)
        FROM (
                SELECT DISTINCT JSON_ARRAY_ELEMENTS(value)::TEXT AS year
                FROM JSON_EACH(hobby_years)
                WHERE key IN ('soccer', 'basketball')) years
        ) AS count
FROM users

推荐阅读