Advertisement
Guest User

Untitled

a guest
Jun 20th, 2019
96
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.94 KB | None | 0 0
  1. {
  2. "participants": [
  3. {
  4. "name": "xxxx"
  5. },
  6. {
  7. "name": "xxxx"
  8. }
  9. ],
  10. "messages": [
  11. {
  12. "sender_name": "xxxx",
  13. "timestamp_ms": 1xxxx,
  14. "content": "xxxx",
  15. "type": "xxxx"
  16. },
  17. {
  18. "sender_name": "xxxx",
  19. "timestamp_ms": xxxx,
  20. "content": "xxxx",
  21. "type": "xxxx"
  22. }
  23. ],
  24. "title": "xxxx",
  25. "is_still_participant": xxxx,
  26. "thread_type": "xxxx",
  27. "thread_path": "xxxxx"
  28. }
  29.  
  30. psql
  31. select ab.value, tb.messagenumber, message:: json ->> 'title' as title
  32. FROM public.ImportTable as tb, json_array_elements(message:: json ->
  33. 'messages') as ab
  34.  
  35. Message array 1 | MessageID 1|Title 1
  36. Message array 2 | MessageID 1|Title 1
  37. Message array 3 | MessageID 1|Title 1
  38. Message array 4 | MessageID 1|Title 1
  39. Message array 1 | MessageID 2|Title 2
  40. Message array 2 | MessageID 2|Title 2
  41.  
  42. psql
  43. SELECT * FROM public.jsonmessage as tb, json_each_text(Message array::
  44. json)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement