I have a table called goals where each row contains 1 goal. The data about specific metrics for the goal is stored in ARRAY of JSON called goal_metrics. Sometimes there is 1 metric, sometimes there are 0 metrics, other times there are multiple metrics. I want to extract all of the goal metrics into a new table, with 1 line per metrics and the original goal_id that metric belongs to.
This is what table1 looks like currently:
+---------+------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| goal_id | goal_owner | goal_create_at | goal_metrics |
+---------+------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 69660 | 148191 | 1566576355 | [{'metric_owner': '148191', 'metric_create_at': '1566576374', 'metric_target': '10'}, {'metric_owner': '148191', 'metric_create_at': '1566576403', 'metric_target': '1'}, {'metric_owner': '148191', 'metric_create_at': '1566576428', 'metric_target': '3'}, {'metric_owner': '148191', 'metric_create_at': '1566576450', 'metric_target': '3'}] |
| 68443 | 146270 | 1565911160 | [{'metric_owner': '144534', 'metric_create_at': '1565911175', 'metric_target': '1'}, {'metric_owner': '144352', 'metric_create_at': '1565911191', 'metric_target': '1'}, {'metric_owner': '144352', 'metric_create_at': '1565911212', 'metric_target': '15'}, {'metric_owner': '146270', 'metric_create_at': '1565911353', 'metric_target': '23'}] |
| 68440 | 146270 | 1565910356 | [{'metric_owner': '144061', 'metric_create_at': '1565910380', 'metric_target': '3'}, {'metric_owner': '144061', 'metric_create_at': '1565910462', 'metric_target': '0'}, {'metric_owner': '144534', 'metric_create_at': '1565910523', 'metric_target': '1'}, {'metric_owner': '143866', 'metric_create_at': '1565911422', 'metric_target': '6'}] |
| 68442 | 146270 | 1565910746 | [{'metric_owner': '143866', 'metric_create_at': '1565910863', 'metric_target': '9'}, {'metric_owner': '143866', 'metric_create_at': '1565910881', 'metric_target': '1'}, {'metric_owner': '144534', 'metric_create_at': '1565910905', 'metric_target': '1'}, {'metric_owner': '146270', 'metric_create_at': '1565910927', 'metric_target': '1'}, {'metric_owner': '144534', 'metric_create_at': '1565910956', 'metric_target': '1'}] |
+---------+------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
And this is what I’m trying to get to:
+--------------+------------------+---------------+---------+ | metric_owner | metric_create_at | metric_target | goal_id | +--------------+------------------+---------------+---------+ | 143321 | 1557774894 | 56 | 53513 | | 143321 | 1557774933 | 100 | 53513 | | 143321 | 1557774983 | 10 | 53513 | | 143321 | 1557775102 | 100 | 53513 | | 143321 | 1557775388 | 100 | 53513 | | 148191 | 1566576374 | 10 | 69660 | | 148191 | 1566576403 | 1 | 69660 | | 148191 | 1566576428 | 3 | 69660 | | 148191 | 1566576450 | 3 | 69660 | | 144534 | 1565911175 | 1 | 68443 | | 144352 | 1565911191 | 1 | 68443 | | 144352 | 1565911212 | 15 | 68443 | | 146270 | 1565911353 | 23 | 68443 | | 144061 | 1565910380 | 3 | 68440 | | 144061 | 1565910462 | 0 | 68440 | | 144534 | 1565910523 | 1 | 68440 | | 143866 | 1565911422 | 6 | 68440 | | 143866 | 1565910863 | 9 | 68442 | | 143866 | 1565910881 | 1 | 68442 | | 144534 | 1565910905 | 1 | 68442 | | 146270 | 1565910927 | 1 | 68442 | | 144534 | 1565910956 | 1 | 68442 | +--------------+------------------+---------------+---------+
If it was just a regular JSON column, I could use SELECT goal_metric -> 'metrics_owner' AS metrics_owner, but because it’s an ARRAY of variable length, that won’t work. I think that I need to use JSON_TO_RECORDSET, and I’ve been experimenting with that, but I haven’t been able to achieve what I’m looking for.
Advertisement
Answer
There are different possible ways (Postgres JSON documentation):
A: Using json_array_elements()
SELECT
goal_id,
(elems ->> 'metric_owner')::int AS metric_owner,
(elems ->> 'metric_create_at')::int AS metric_create_at,
(elems ->> 'metric_target')::int AS metric_target
FROM
table1,
json_array_elements(goal_metrics) as elems
This expands every array element into one row. These expanded JSON objects can be queried for their attributes. This gives out text types. If you want them to be int types, you need a cast.
B: Using json_to_recordset()
SELECT
goal_id,
elems.*
FROM
table1,
json_to_recordset(goal_metrics)
AS elems(metric_owner int, metric_create_at int,metric_target int)
This does the same as in (A) in one step. You just need to define the output types.
C: If you have to do this operation regularly, it might be useful to define the output type once. Then you can use it within json_populate_recordset():
CREATE TYPE metrics_type
AS (metric_owner int, metric_create_at int,metric_target int);
SELECT
goal_id,
elems.*
FROM
table1,
json_populate_recordset(null::metrics_type, goal_metrics) AS elems