Advertisement
kromm77

postgres json

Feb 27th, 2015
313
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.71 KB | None | 0 0
  1.  CREATE TABLE test_json ( a json, id serial);
  2. INSERT INTO test_json (a) VALUES ( '{"a":"foo", "b":"bar"}' );
  3.  
  4. SELECT * FROM test_json WHERE a->>'b' = 'bar';
  5.  
  6.  
  7. SELECT * FROM test_json WHERE a->a='foo';
  8.  
  9.  
  10. ALTER TABLE test_json ADD json_data json ;
  11.  
  12. --
  13. CREATE TEMP TABLE x (aa text,bb TIMESTAMP );SELECT json_agg(( phonenumber,created_at )::x ) FROM (SELECT phonenumber,created_at  FROM inventory.phonenumber  WHERE json_data->>'Phonenumber'>'0426085000' LIMIT 3 ) a
  14.  
  15. -[ RECORD 1 ]------------------------------------------------
  16. json_agg | [{"aa":"04611920056","bb":"2015-03-03 08:20:12"},
  17.          |  {"aa":"0462770051","bb":"2015-03-03 08:20:12"},
  18.          |  {"aa":"0462770303","bb":"2015-03-03 08:20:12"}]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement