Count unique nested key/values in a json in postgres?

Question

How to count the unique nested key/values in a json in postgres?

Each user record has a json column called hobby_years which is structured like below but differs from user to user.

Example hobby_years value for a user:

{
    "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
    }
}

I'd like to know how many unique years exist across one or more specific hobbies. Ie: soccer and basketball - which should return 5.

Alternatively, I have the option to structure data as below if it would make this activity easier

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

Is this possible?

404 · Accepted Answer · 2018-05-17 17:11:38Z

1

Something like this?

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

edited May 17, 2018 at 17:11

answered May 17, 2018 at 16:33

404

8,7622 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count unique nested key/values in a json in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related