Postgresql – get inherit keys inside json

jsonpostgresql

I have JSON field with values like in the example, and I need to get all data from the key 'stat' inside it. The list of keys inside the data not constant.

WITH test_data AS (
SELECT 1 as id,
    '{
      "calm_beach_reef": {"full_sessions": 0, "sessions": []}, 
      "island_of_tranquility": {"full_sessions": 0, "sessions": []}, 
      "peaceful_forest": {"full_sessions": 0, "sessions": []}, 
      "aurora_in_the_arctic": {"full_sessions": 0, "sessions": []}, 
      "serene_space": {"full_sessions": 0, "sessions": []}
     }'::json AS data
UNION ALL
SELECT 2,
    '{
      "calm_beach_reef": {"full_sessions": 0, "sessions": []}, 
      "island_of_tranquility": {"full_sessions": 0, "sessions": []}, 
      "peaceful_forest": {"full_sessions": 0, "sessions": []}, 
      "aurora_in_the_arctic": {"full_sessions": 0, "sessions": []}, 
      "serene_space": {"full_sessions": 0, "sessions": []}
     }'::json
UNION ALL
SELECT 3,
    '{
      "calm_beach_reef": {"full_sessions": 0, "sessions": [{"percent_from": 0, "percent": 0, "stat": "61/71#61/71,61/71#92/83,90/78#102/82,80/73#90/78,82/72#99/74,92/71#99/75,93/62#95/71,92/53#95/62,86/50#107/53", "completed_at": "2017-11-27 14:29:22.878709"}]}, 
      "island_of_tranquility": {"full_sessions": 0, "sessions": []}, 
      "peaceful_forest": {"full_sessions": 0, "sessions": []}, 
      "aurora_in_the_arctic": {"full_sessions": 0, "sessions": []}, 
      "serene_space": {"full_sessions": 0, "sessions": []}
     }'::json
UNION ALL
SELECT 4,
    '{
      "calm_beach_reef": {"full_sessions": 0, "sessions": [{"percent_from": 0, "percent": 1, "stat": "42/21#42/21,42/21#89/24,77/24#95/26,86/13#95/25,92/6#96/13,62/6#93/8,61/8#62/10,61/10#61/12,0/12,0/16,0/21,0/12#61/33,0/28#0/34,0/23#0/28,0/20#0/23,0/18#0/20,0/18#0/18,0/18#0/24,0/24#0/25,0/23#0/24,0/21#0/23,0/18#0/21,0/12#0/18,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12,0/15,0/12#47/21,0/21#0/25,0/25#0/29,0/29#0/33,0/30#0/35,0/27#0/30,0/27#0/34,0/31#0/35,0/27#0/31,0/25#0/28,0/28#0/31,0/24#0/31,0/23#0/25,0/25#0/27,0/25#0/28,0/19#0/24,0/16#0/19", "completed_at": "2018-08-20 12:18:14.404032"}]}, 
      "island_of_tranquility": {"full_sessions": 0, "sessions": []}, 
      "peaceful_forest": {"full_sessions": 0, "sessions": []}, 
      "aurora_in_the_arctic": {"full_sessions": 0, "sessions": []}, 
      "serene_space": {"full_sessions": 0, "sessions": []}
     }'::json
UNION ALL
SELECT 5,
    '{
      "calm_beach_reef": {"full_sessions": 0, "sessions": []}, 
      "island_of_tranquility": {"full_sessions": 0, "sessions": []}, 
      "peaceful_forest": {"full_sessions": 0, "sessions": []}, 
      "aurora_in_the_arctic": {"full_sessions": 0, "sessions": []}, 
      "serene_space": {"full_sessions": 0, "sessions": [{"percent_from": 0, "percent": 74, "stat": "50/41#50/41,48/41#50/43,48/41#48/43,0/45,0/43#48/54,0/54#52/54,52/54#52/55,0/54,0/54#52/56,0/51#0/55,0/50#0/51,0/50#0/51,0/51#63/56,63/56#71/61,69/61#72/64,69/63#69/66,66/64#69/65,66/61#71/64,72/53#80/61,75/46#80/53,72/43#75/46,69/36#75/43,75/31#79/36,75/30#79/31,71/31#73/32,69/31#78/32,69/32#78/32,64/32#69/33,64/33#70/33,70/33#70/33,70/33#70/33,70/33#74/35,74/35#75/40,75/40#75/45,75/45#75/50,75/50#76/56,74/56#79/60,79/60#79/64,79/64#79/67,75/67#79/68,69/68#75/68,65/68#69/68,67/54#78/68,73/45#78/54,72/42#76/45,76/39#77/42,73/34#77/38,72/29#73/34,73/21#75/29,74/11#76/21,75/6#81/11,77/4#81/6,74/3#77/6,75/6#79/10,75/10#78/15,74/15#78/22,78/22#82/25,74/25#82/27,72/27#74/31,72/25#72/31,71/22#72/25,71/23#80/28,80/28#85/37,72/37#84/48,69/48#72/67,0/74,0/67#69/75,0/66#0/71,0/60#0/66,0/55#67/60,63/53#64/55,64/53#64/55,64/53#64/56,64/45#64/53,64/44#64/48,62/48#64/56,62/56#62/63,0/64,0/63#62/65,0/65#0/75", "completed_at": "2018-08-08 18:57:51.990080"}]}
     }'::json
), keys AS (
    SELECT 
        json_object_keys(data) as key,
        id
    FROM test_data
)

, keys AS (
            SELECT 
               json_object_keys(data) as key,
               id
            FROM test_data
          )
 SELECT json_array_elements(gs.data->key->'sessions')->'stat',
     key
 FROM test_data gs
     JOIN keys ON keys.id = gs.id
 WHERE gs.data->key->'sessions' IS NOT NULL
     AND (gs.data->key->'sessions')::jsonb != '[]'

as you see I do the multiple join to the table to it self, is it right way or may be has other best solution?

More details: in the table json field, current version of the server is 9.4

Best Answer

I'm not sure if it fits what are you looking for:

SELECT 
    json_array_elements(dt.value::json->'sessions')->'stat'
FROM
    test, json_each(data) dt

| ?column?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| "61/71#61/71,61/71#92/83,90/78#102/82,80/73#90/78,82/72#99/74,92/71#99/75,93/62#95/71,92/53#95/62,86/50#107/53"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| "42/21#42/21,42/21#89/24,77/24#95/26,86/13#95/25,92/6#96/13,62/6#93/8,61/8#62/10,61/10#61/12,0/12,0/16,0/21,0/12#61/33,0/28#0/34,0/23#0/28,0/20#0/23,0/18#0/20,0/18#0/18,0/18#0/24,0/24#0/25,0/23#0/24,0/21#0/23,0/18#0/21,0/12#0/18,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12#0/12,0/12,0/15,0/12#47/21,0/21#0/25,0/25#0/29,0/29#0/33,0/30#0/35,0/27#0/30,0/27#0/34,0/31#0/35,0/27#0/31,0/25#0/28,0/28#0/31,0/24#0/31,0/23#0/25,0/25#0/27,0/25#0/28,0/19#0/24,0/16#0/19"                                                                                                                                                                                                                           |
| "50/41#50/41,48/41#50/43,48/41#48/43,0/45,0/43#48/54,0/54#52/54,52/54#52/55,0/54,0/54#52/56,0/51#0/55,0/50#0/51,0/50#0/51,0/51#63/56,63/56#71/61,69/61#72/64,69/63#69/66,66/64#69/65,66/61#71/64,72/53#80/61,75/46#80/53,72/43#75/46,69/36#75/43,75/31#79/36,75/30#79/31,71/31#73/32,69/31#78/32,69/32#78/32,64/32#69/33,64/33#70/33,70/33#70/33,70/33#70/33,70/33#74/35,74/35#75/40,75/40#75/45,75/45#75/50,75/50#76/56,74/56#79/60,79/60#79/64,79/64#79/67,75/67#79/68,69/68#75/68,65/68#69/68,67/54#78/68,73/45#78/54,72/42#76/45,76/39#77/42,73/34#77/38,72/29#73/34,73/21#75/29,74/11#76/21,75/6#81/11,77/4#81/6,74/3#77/6,75/6#79/10,75/10#78/15,74/15#78/22,78/22#82/25,74/25#82/27,72/27#74/31,72/25#72/31,71/22#72/25,71/23#80/28,80/28#85/37,72/37#84/48,69/48#72/67,0/74,0/67#69/75,0/66#0/71,0/60#0/66,0/55#67/60,63/53#64/55,64/53#64/55,64/53#64/56,64/45#64/53,64/44#64/48,62/48#64/56,62/56#62/63,0/64,0/63#62/65,0/65#0/75" |

db<>fiddle here

Related Question