PostgreSQL json fields, how to use them?
badmansan opened this issue · 4 comments
Version: 3.0.5
Bug Description
I want to use json field in WHERE
Steps To Reproduce
Create simple table:
CREATE TABLE "log" (
"data" json NOT NULL
);
INSERT INTO "log" ("data") VALUES
('{"code":2}');
Try to run query:
$db->query('SELECT * FROM log WHERE', [
"data->>'code'" => 2,
]);
Expected Behavior
Result SQL must be (query in postgres_query.log)
SELECT * FROM log WHERE (data->>'code' = $1)
but I got
ERROR: column "data->>'code'" does not exist at character 26
STATEMENT: SELECT * FROM log WHERE ("data->>'code'" = $1)
Possible Solution
Nette need to somehow avoid quotes in the name of such fields
Because keys are always quoted, I think the best way is to use Nette\Database\SqlLiteral
$db->query('SELECT * FROM log WHERE', [
new SqlLiteral("data->>'code' = ?", [2]),
]);
Maybe you're right. I just don't like how the resulting code looks in case of complex queries.
$i = 0;
foreach ($keyIds as $key) {
$literal = [
$this->db::literal("data->>'code' = ?", $event),
$this->db::literal("data->'data'->>'key' = ?", $key),
];
if ($result) {
$literal[] = $this->db::literal("data->'data'->>'result'", $result);
}
$params[$i++] = $this->db::literal('?and', $literal);
}
$this->query($sql, $this->db::literal('?and', $params));
instead of
foreach ($keyIds as $key) {
$params = [
"data->>'ev_code'" => $event,
"data->'data'->>'key'" => $key,
];
if ($result) {
$params[] = ["data->data'->>'result'" => $result];
}
}
$this->query($sql, ...$params);
Yes, problem is that data->>'code'
is not column, it is column data
and JSON operator ->>
and string 'code'
. And NDB is not ready for it. Support for JSON could certainly be added…
I have similar problem. When filtering with fields->>'myNote' LIKE (?)
it wraps myNote with quotes "fields"->>'"myNote"' LIKE (?)
. So I need to go with SqlLiteral for now. Support for these specialities would be nice.