Reputation: 193
I have a Postgres table that has a field, KPI, containing a JSON object. It contains data for every week of the year. I want to return the values contained for the current week, in this case w5
which is always the last item in the JSON.
Extract from KPI field
{
"details":{
"w52":{
"sales-goal":96.0,
"sales":81.2,
"forecast-goal":96.0,
"forecast":83.6
},
"w3":{
"sales-goal":96.0,
"sales":85.8,
"forecast-goal":96.0,
"forecast":85.7
},
"w4":{
"sales-goal":96.0,
"sales":86.3,
"forecast-goal":96.0,
"forecast":86.1
},
"w5":{
"sales-goal":96.0,
"sales":86.6,
"forecast-goal":96.0,
"forecast":86.0
}
}
}
This is the SQL I have
SELECT
kpi->'details'::json #>>(json_array_length(col->'details'::json)-1)#>> '{sales-goal}'::text[] as Sales Goal,
kpi->'details'::json #>>(json_array_length(col->'details'::json)-1)#>> '{sales}'::text[] as Sales
FROM areas
And I get the following error message:
ERROR: invalid input syntax for type json Detail: Token "details" is invalid. Position: 14 Where: JSON data, line 1: details
I'm trying to get this result.
Sales Goal | Sales
-----------------+------------------------
96.0 | 86.6
Upvotes: 0
Views: 115
Reputation: 23766
If you want to get always the last entry (not the specific w5
element):
In fact, there is no guarantee for a specific order of elements in a JSON object. If you want to risk that, this could be a solution:
SELECT
elems.value ->> 'Sales Goal' as sales_goal,
elems.value ->> 'Sales' as sales -- 3
FROM mytable,
json_each(kpi) WITH ORDINALITY as elems(value, index) -- 1
ORDER BY elems.index DESC -- 2
LIMIT 1
Upvotes: 1