Advertisement
Guest User

Untitled

a guest
Mar 20th, 2019
56
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.86 KB | None | 0 0
  1. CREATE TABLE jsonbtest (data jsonb);
  2.  
  3. INSERT INTO jsonbtest (data) VALUES ('{"city":"Mesa","county":"Maricopa","mayor":"John Smith"}');
  4. INSERT INTO jsonbtest (data) VALUES ('{"other city":"Mesa but with a longer name","county":"Maricopa","mayor":"John Smith Jr"}');
  5.  
  6. SELECT (jsonb_each_text(data)).* FROM jsonbtest;
  7.  
  8. key │ value
  9. ────────────┼────────────
  10. city │ Mesa
  11. mayor │ John Smith
  12. county │ Maricopa
  13. mayor │ John Smith
  14. county │ Maricopa
  15. other city │ Mesa
  16. (6 rows)
  17.  
  18. SELECT key, max(char_length(value))
  19. FROM (SELECT (jsonb_each_text(data)).* FROM jsonbtest) t
  20. GROUP BY key;
  21.  
  22. key │ max
  23. ────────────┼─────
  24. city │ 4
  25. other city │ 27
  26. county │ 8
  27. mayor │ 13
  28. (4 rows)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement