Postgresql - Sorting on a nested field in a jsonb object whose parent key can be multiple values

tehawtness
August 7, 2024
70 views
0 votes
2 Answers

I have a field that is of type jsonb that is structured like:

data: {
  "prices": {
    "[parent key]": {
        "price": 20,
    }
  }
}

‘parent key’ can be 5 possible values but all I really need is the ‘price’ field. Ultimately I would like to extract that price field as well as order the results by that field. Is there a way that this can be done?

Example. I would like to get the price for each entry in descending order according to price. If an entry has more than one parent key for ‘price’, select the larger of the two.

[{
  id: 1,
  data: {
    "prices": {
      "x": {
         "price": 20,
      }
    }
  }
},
{
  id: 2,
  data: {
    "prices": {
      "y": {
         "price": 86,
      }
    }
  }
},
{
  id: 3,
  data: {
    "prices": {
      "z": {
         "price": 21,
      },
      "b": {
         "price": 41,
      }
    }
  }
}

would return

[
  {
    id: 2,
    price: 86
  },
  {
    id: 3,
    price: 41
  },
  {
    id: 1,
    price: 20
  }
]

Answers

- jjanes
- August 5, 2024 at 9:23 pm
- 0 votes
0
It sounds like you want to use the jsonpath feature. That is, jsonb_path_query with the 2nd argument ‘$.data.prices.*.price’. But it isn’t clear what you want to happen if there is more than one, or less than one, match.

Login or Signup to reply.

- PepeNO
- August 7, 2024 at 9:28 pm
- 0 votes
0
Just disassemble the JSON array, select id and price(by level), then with a window function get the max price for a correspondent id, like:
```
select jsonb_agg(row_to_json(t)) resulting_json
from (
with jsonb_data as (
    select jsonb_array_elements(data_field) df 
    from example
)
select 
    df ->> 'id' id, 
    max(jsonb_path_query_first(df, '$.**{4}')::integer) 
    over (partition by df -> 'id') price    
from jsonb_data
order by 2 desc) t;
```
resulting_json

[{"id": "2", "price": 86}, {"id": "3", "price": 41}, {"id": "1", "price": 20}]

Fiddle to test
Login or Signup to reply.