CODEWITHSUNDEEP
mysqljsondbeaver
MarcusR1
MarcusR1

Reputation: 39

JSON_CONTAINS example not working on DBeaver mysql version 8.0.33

I'm trying to get JSON_CONTAINS to work on a Digital Ocean droplet, executing

select version();

gives me 8.0.33-0ubuntu0.20.04.2

And I'm trying to use the example from the documentation that says it should work https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-contains

but the code I pasted from the example:

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SET @j2 = '1';
SELECT JSON_CONTAINS(@j, @j2, '$.a');

returns NULL

Can anyone help?

Update: here's a screenshot of DBeaver showing the NULL enter image description here

Update: It transpired that I was at fault, I was not executing the whole script, so the Set commands were not being executed, so the Select command had nothing to work on. Thank you to DBeaver Support!

enter image description here

Upvotes: 0

Views: 412

Answers (1)

BOB
BOB

Reputation: 273

Try JSON_EXTRACT():

SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
SELECT JSON_EXTRACT(@j, "$.a");

return 1

SET @j = '{"a": "foo", "b": 2, "c": {"d": 4}}';
SELECT JSON_EXTRACT(@j, "$.a");

return "foo"

If you create a table with the following DDL

CREATE TABLE `bar` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `changed_data` json DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

and inserted the following values

INSERT INTO bar (changed_data)
    VALUES ('{"a": 1, "b": 2, "c": {"d": 4}}');
INSERT INTO bar (changed_data)
    VALUES ('{"a": 99,"b": 777,"c": {"d": 888}}');

You could get the values like this:

SELECT 
   x.*,
   JSON_EXTRACT(changed_data, "$.c.d") as value_of_d 
FROM bar x
WHERE 
    (JSON_EXTRACT(changed_data, "$.a") LIKE '%9%') 
AND 
    (JSON_EXTRACT(changed_data, "$.c.d") LIKE '%8%')

you will get:

id changed_data timestamp value_of_d
2 {"a": 99, "b": 777, "c": {"d": 888}} 2025-02-03 17:46:16 888

enter image description here

Upvotes: 0

Related Questions