Guest User

Untitled

a guest
Sep 24th, 2018
84
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.75 KB | None | 0 0
  1. jsonb_each_text(jsonb_column) ILIKE '%val%'
  2.  
  3. SELECT
  4. '{
  5. "col1": "somevalue",
  6. "col2": 5.5,
  7. "col3": 2016-01-01,
  8. "col4": "othervalue",
  9. "col5": "yet_another_value"
  10. }'::JSONB
  11.  
  12. create table foo as SELECT '{"col1": "somevalue", "col2": 5.5, "col3": "2016-01-01", "col4": "othervalue", "col5": "yet_another_value"}'::JSONB as bar;
  13.  
  14. create index pickfoo1 on foo ((bar #>> '{col1}'));
  15. create index pickfoo2 on foo ((bar #>> '{col2}'));
  16.  
  17. -- Create index over lowered value:
  18. create index pickfoo1 on foo (lower(bar #>> '{col1}'));
  19. create index pickfoo2 on foo (lower(bar #>> '{col2}'));
  20.  
  21. -- Check that it matches:
  22. select * from foo where lower(bar #>> '{col1}') = lower('soMEvaLUe');
  23.  
  24. select * from foo where bar #>> '{col1}' ilike '%MEvaL%';
Add Comment
Please, Sign In to add comment