我正在使用postgres數據庫作為我的應用程序。
我有以下數據
create table contacts (
id int,
contact_data jsonb
);
insert into contacts values
(1, '{
"tags": [
"MOCK_DATA (4)"
],
"Email": "nk3ynman5@marriott.com",
"reach": false,
"Gender": "Male",
"Interest": [
"Dance"
],
"Last Name": "Kynman",
"First Name": "Nicko"
}'),
(2, '{
"ltv": "6",
"City": "Bengaluru",
"Email": "aaa@aa.com",
"State": "Karnataka",
"Country": "India",
"latitude": 12.9715987,
"Last Name": "World",
"longitude": 77.5945627,
"First Name": "Helo"
}'),
(3, '{
"ltv": "11",
"Email": "sf3ortye8@ebay.co.uk",
"reach": false,
"Gender": "Female",
"Country": "United States",
"latitude": "37.09024",
"Last Name": "Fortye",
"longitude": "-95.712891",
"First Name": "Sissie"
}');
Select * from contacts;
+====+==================================================================================================================================+
| id | contact_data |
+====+==================================================================================================================================+
| 1 | {"tags": ["MOCK_DATA (4)"], "Email": "nk3ynman5@marriott.com", "reach": false, "Gender": "Male", "Interest": ["Dance"], "Last... |
+----+----------------------------------------------------------------------------------------------------------------------------------+
| 2 | {"ltv": "6", "City": "Bengaluru", "Email": "aaa@aa.com", "State": "Karnataka", "Country": "India", "latitude": 12.9715987, "L... |
+----+----------------------------------------------------------------------------------------------------------------------------------+
| 3 | {"ltv": "11", "Email": "sf3ortye8@ebay.co.uk", "reach": false, "Gender": "Female", "Country": "United States", "latitude": "3... |
+----+----------------------------------------------------------------------------------------------------------------------------------+
我正在嘗試獲取ltv在2到9之間的id。它有一張唱片。哪個是正確的
SELECT id from contacts where ( contact_data->> 'ltv' > '2' and contact_data->> 'ltv' < '9' );
+====+
| id |
+====+
| 2 |
+----+
Issue:
現在我嘗試在2到12之間,這應該會給出2條記錄,但是它會給出0個數據。
SELECT id from contacts where ( contact_data->> 'ltv' > '2' and contact_data->> 'ltv' < '12' );
誰能告訴我這里有什么問題嗎。為什么我得不到價值?這里是sql-fiddle
在PostgreSQL中,
->>
運算符將返回一個text
值。所以你也用了相應的類型。嘗試以下查詢:
演示中唯一需要注意的是,
itv
必須只包含整數值,否則強制轉換將拋出錯誤。