LoginSignup
0
1

More than 3 years have passed since last update.

投稿テスト4(PostgreSQLでJSONB型を扱う)

Last updated at Posted at 2021-01-11

PostgreSQLでJSONB型を取扱う際のコードまとめ

参考にしたページ
https://blog.serverworks.co.jp/postgres_jsonb_search
https://qiita.com/kumazo/items/483f47360f8b61a9fbb9

テスト用の環境

テスト用のテーブル作成 & テストデータ投入

CREATE TABLE jsonb_test(
    id int,
    jsonb jsonb
);

INSERT INTO jsonb_test VALUES(
    (1000,'{"no":1,"state":"great","data1":36.5,"data2":1.11}'),
    (1001,'{"no":2,"state":"good","data1":36.0,"data2":2.22}'),
    (1002,'{"no":3,"state":"bad","data1":37.4,"data2":3.33}'),
    (1003,'{"no":4,"state":"good","data1":36.4,"data2":4.44}'),
    (1004,'{"no":5,"state":"bad","data1":38.1,"data2":5.55}')

キー「state」のデータを取り出す

「->>」でキーにアクセスする。
「state」にアクセスした場合

select jsonb->>'state' from jsonb_test

TEXT型
"great"
"good"
"bad"
"good"
"bad"

Where句にJSONB型部分を指定する場合

※そのまま抜き出すとTEXTになってしまう

select jsonb->>'data1' from jsonb_test 
      where jsonb->>'state' = 'good'

TEXT型
"36.0"
"36.4"

抽出したデータはTEXT型になるため、任意の方にキャストして使用

select (jsonb->>'data1')::NUMERIC
   from jsonb_test where jsonb->>'state'='good'

NUMERIC型
36.0
36.4

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
1