CODEWITHSUNDEEP
sqljsonpostgresql
Malyk
Malyk

Reputation: 193

Retrieve values from the last object in a JSON object in Postgres

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

Answers (1)

S-Man
S-Man

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
  1. Expand the JSON elements into one record per element. Add an index to be able to sort them in next step
  2. Order "last" (see notice above!) element to top and limit output to one to return only the "last" record
  3. Return the values.

Upvotes: 1

Related Questions