79603788

Date: 2025-05-02 16:53:15
Score: 2
Natty:
Report link

Thanks all, much appreciated!

I did also have a requirement to extract a specific attribute based on another column, and this helped me solve for it. Here it is for posterity:

WITH pets AS (SELECT 'Lizard' AS species, '
{
  "Dog": {
    "mainMeal": "Meat",
    "secondaryMeal": "Nuggets"
  },
  "Cat": {
    "mainMeal": "Milk",
    "secondaryMeal": "Fish"
  },
  "Lizard": {
    "mainMeal": "Insects",
    "secondaryMeal": "None"
  }
}'::jsonb AS petfood)
SELECT
    pets.petfood,
    jsonb_path_query_first(
        pets.petfood,('$."'|| pets.species::text||'"."mainMeal"')::jsonpath
    ) ->> 0 as mypetmainmeal
FROM pets
Reasons:
  • Blacklisted phrase (0.5): Thanks
  • Blacklisted phrase (1): appreciated
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (1):
Posted by: Dhruva Sen Gupta