mysql - PostgreSQL 查询 : distinct count of jsonb values column

Question

我有一个名为 mainapp_project_data 的表，其中有一个 jsonb 列 project_user_data

• 表格

 public | mainapp_project_data | table | admin

---

select project_user_data from mainapp_project_data;

                                         project_user_data

-----------------------------------------------------------------------------------------------------------------
 [{"name": "john", "age": "21", "gender": "M"}, {"name": "randy", "age": "23", "gender": "M"}]
 [{"name": "donald", "age": "31", "gender": "M"}, {"name": "wick", "age": "32",
"gender": "M"}]
 [{"name": "orton", "age": "18", "gender": "M"}, {"name": "russel", "age": "55",
"gender": "M"}]
 [{"name": "angelina", "age": "open", "gender": "F"}, {"name": "josep", "age": "21",
"gender": "M"}]
(4 rows)

(END)

• 我想计算 JSON 的键 gender 和 age 的不同值。

输出格式:[{key:count(repeated_values)}]

filtering on `gender` : [{"M":7},{"F":1}]
filtering on `age` : [{"21":2},{"23":1},{"31":1}.....]

Answer 1

WITH flat AS (
  SELECT
    kv.key,
    -- make into a JSON object with a single value and count, e.g., '{"M": 7}'
    jsonb_build_object(kv.value, COUNT(*)) AS val_count
  FROM mainapp_project_data AS mpd
    -- Flatten the JSON arrays into single objects per row
    CROSS JOIN LATERAL jsonb_array_elements(mpd.project_user_data) AS unarrayed(udata)
    -- Convert to a long, flat list of key-value pairs
    CROSS JOIN LATERAL jsonb_each_text(unarrayed.udata) AS kv(key, value)
  GROUP BY kv.key, kv.value
)
SELECT
  -- de-deplicated object keys
  flat.key,
  -- aggregation of all values and counts per key
  jsonb_agg(flat.val_count) AS value_counts
FROM flat
GROUP BY flat.key

返回

  key   |                                                  value_counts
--------+---------------------------------------------------------------------------------------------------------------------
 gender | [{"M": 7}, {"F": 1}]
 name   | [{"josep": 1}, {"russel": 1}, {"orton": 1}, {"donald": 1}, {"wick": 1}, {"john": 1}, {"randy": 1}, {"angelina": 1}]
 age    | [{"18": 1}, {"32": 1}, {"21": 2}, {"23": 1}, {"open": 1}, {"31": 1}, {"55": 1}]

这将提供任何键值对实例计数。如果您只需要性别和年龄，只需在第一个 GROUP BY 子句之前添加一个 where 子句。

  WHERE kv.key IN ('gender', 'age')