MySQL Select * FROM table WHERE a column's (JSON Object) specific value's string length is-CodePudding

I need to return all records from a table where the submission column has an empty 'email' value in the JSON object. The query should only return records 15 and 17 from the table below.

  ------------ -------------------- ---------------------------------- 
 | id | name               | submission                               |
  ------------ -------------------- ---------------------------------- 
 | 13 | Chris Sale         | {"gender":"m"},{"email":"[email protected]"}   |  
 | 14 | Amy Verlander      | {"gender":"f"},{"email":"[email protected]"}   |  
 | 15 | CC Sabathia        | {"gender":"f"},{"email":""}              |  
 | 16 | Sonny Grey         | {"gender":"m"},{"email":"[email protected]"}   |  
 | 17 | Jen Chapman        | {"gender":"m"},{"email":""}              |  
  ------------ -------------------- ----------------------------------

Thanks for your help, Todd

CodePudding user response：

You should use this:

SELECT * FROM your_tb where submission NOT like N'%"gender": ""%';

This where condition check the gender not empty if you need to check null value. You can try this query:

SELECT * FROM your_tb where submission NOT like N'%"gender": ""%' and submission NOT like N'%"gender": ""%';

CodePudding user response：

Your description is no valid JSON, which you can test at jsonlint

This would only test if there is something that looks like a email, but with no accuracy if it really exists

CREATE TABLE table1 (
  `id` INTEGER,
  `name` VARCHAR(13),
  `submission` TEXT
);

INSERT INTO table1
  (`id`, `name`, `submission`)
VALUES
  ('13', 'Chris Sale', '[{"gender":"m"},{"email":"[email protected]"}]'),
  ('14', 'Amy Verlander', '[{"gender":"f"},{"email":"[email protected]"}]'),
  ('15', 'CC Sabathia', '[{"gender":"f"},{"email":""}]'),
  ('16', 'Sonny Grey', '[{"gender":"m"},{"email":"[email protected]"}]'),
  ('17', 'Jen Chapman', '[{"gender":"m"},{"email":""}]');

SELECT * FROM table1 WHERE submission->>"$[*].email"  NOT REGEXP '^[^@] @[^@] \.[^@]{2,}$'

id | name        | submission                   
-: | :---------- | :----------------------------
15 | CC Sabathia | [{"gender":"f"},{"email":""}]
17 | Jen Chapman | [{"gender":"m"},{"email":""}]

db<>fiddle here

CodePudding user response：

Try the code below and see if it helps

SELECT * FROM table WHERE CHAR_LENGTH(submission) > 1

Ref: https://www.w3schools.com/sql/func_mysql_char_length.asp