0

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

1

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

  • Thank you for the help, but in this solution you miss the key serene_space in the 5 row, i don't know full list of the available keys. – Bear Brown Feb 18 at 15:00
  • I see, stat can be stored at any level – McNets Feb 18 at 15:02
  • No, fortunately, this option is static. – Bear Brown Feb 18 at 15:04
  • thanks again, i need to look at the performance but it looks good! – Bear Brown Feb 18 at 15:50
  • I'm glad to help. – McNets Feb 18 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.