Home>

Need help to search for the search key JSON field MySQL.

Input: Massows array of keys (string)

Output: all records with MySQL tables that contain in the field Copmetence (JSON format), input keys.

Examples:
1. Pour an array -["test"]. Must get a record with id= 2.

2. Massion -["One", "Two", "Three"]. Must get records with id= 7.8

3. Massive array -["One", "Three"]. Must receive records with id= 7,8,9

Link to Fiddle -

Example of my implementation on the NestJS + TypeRM + MySQL V8.0 stack

Async test12 (
    Title: String,
    EntityType: CompetenceSearchType,
  ): Promise <
    {Competencies: Record <
String, Number >
 } [] | {Competence: Competence} []
  ≫
 {
    Const Res= [] AS Array <
{Competencies: Record ≪
String, Number >
 } ≫
;
    If (EntityType=== CompetenceSearchType.activity) {
      Const All: Array <
Activity >
= [];
      Const [Entities, Count]= await this.activityRepository.findandCount ({
        WHERE: {
          Competencies: RAW (()= >
 {
            Return `competencies->
"$. $ {title}" >
 0`
          })
        }
      });
      entities.map (entity= >
 {
        if (! res.includes (entity)) {
          Res.Push (Entity);
        }
      });
    }
    RETURN RES;
  } 

Record format in the Competence field: {"key": 1} -The string is the key, the value is the number. Multiple values ​​can be stored. If you go through the loop, then the duplicate appears and it still searches for only 1 key, but I can not set several search conditions, so I can't know how much I get the keys.

Create Table `Activity` (
  `Int NOT NULL AUTO_INCREMENT,
  `Name` Varchar (255) Collate UTF8MB4_Unicode_ci Not Null,
  `Createdat` DateTime (6) Not Null Default Current_TimeStamp (6),
  `Deletedat` DateTime (6) Default Null,
  `Private` Tinyint Not Null Default '0',
  `Topic` Varchar (255) Collate UTF8MB4_Unicode_ci Default NULL,
  `Description` Text Collate UTF8MB4_Unicode_ci,
  `Authorid` INT NOT NULL,
  `StartDate` DateTime Not Null,
  `EndDate` DateTime Default Null,
  `Location` Varchar (255) Collate UTF8MB4_Unicode_ci Default NULL,
  `Competencies` Json Default Null,
  Primary Key (`id`)
  Key `fk_0e60c1982a77d3e092ef4f3bef9` (` authorid`) /*,
  Constraint `fk_0e60c1982a77d3e092ef4f3bef9` Foreign Key (` Authorid`) References `Users` (` id`) ON DELETE CASCADE
                                                    * /
) Engine= innodb default charset= utf8mb4 collate= utf8mb4_unicode_ci; 
Insert Into` Activity` Values
(1, 'testactivity123', '2021-06-21 05: 14: 44.644150', NULL, 0, NULL, NULL, 18, '2021-06-21 05: 14: 45', NULL, NULL, '{\ "Test ActivityCompetence for CreateCompetence \": 1} '),
(2, 'testactivity123', '2021-06-21 05: 14: 44.644150', NULL, 0, NULL, NULL, 18, '2021-06-21 05: 14: 45', NULL, NULL, '{\ "Test \": 3} '),
(3, 'testactivity123', '2021-06-21 05: 14: 44.644000', NULL, 0, NULL, NULL, 18, '2021-06-21 05: 14: 45', NULL, NULL, '{\ "Test ActivityCompetence for CreateCompetence \": 1} '),
(4, 'Test', '2021-06-21 05: 30: 29.686046', NULL, 0, NULL, NULL, 51, '2021-06-21 05: 30: 30', NULL, NULL, '{\ "CompetenceForSearch \": 20, \ "BLABLA TEXT \": 2} '),
(5, 'Test', '2021-06-21 05: 30: 29.686046', NULL, 0, NULL, NULL, 51, '2021-06-21 05: 30: 30', NULL, NULL, '{\ "FirstCompetence \": 2, \ "FourthCompetence \": 4} '),
(6, 'Test', '2021-06-21 05: 30: 29.686046', NULL, 0, NULL, NULL, 51, '2021-06-21 05: 30: 30', NULL, NULL, '{\ "FirstCompetence \": 2, \ "SecondCompetence \": 2} '),
(7, 'Test', '2021-06-21 05: 30: 29.686046', NULL, 0, NULL, NULL, 51, '2021-06-21 05: 30: 30', NULL, NULL, '{\ "One \": 1, \ "Two \": 2, \ "three \": 3} '),(8, 'Test', '2021-06-21 05: 30: 29.686046', NULL, 0, NULL, NULL, 51, '2021-06-21 05: 30: 30', NULL, NULL, '{\ "One \": 1, \ "Two \": 2, \ "Three \": 3, \ "Four \": 4} '), (9,' Test ',' 2021-06-21 05:30 : 29.686046 ', NULL, 0, NULL, NULL, 51,' 2021-06-21 05: 30: 30 ', NULL, NULL,' {\ "One \": 1, \ "Four \": 4, \ "Three \": 3} '); 

I did not understand ... This is what you seriously -to drain the entire data array to your client, and then filter the right one? Where how easier to give the criterion to MySQL and get recording already filtered on it.

Akina2021-06-21 04:37:09

Well, you will not tell me how then it can be realized if it is unknown, how many criteria for filtering will I have? I can sort 1 key, and I can be alone. The idea is that when sorting several keys, they are looking for those records where they are all.

XtReLL2021-06-21 04:44:18

You first decide, you need to search or need sorting. This is not the same thing.

Akina2021-06-21 04:46:00

How then can this be implemented, if it is unknown, how many criteria for filtering will I have? At the time when it is already necessary to obtain data, their number and values ​​are known, right? If you decide to follow the filter path on the server -post the source data. CREATE TABLE tables in which you want to search, an example of data insert INTO (3-5 entries), an example of a set of criteria for filtering and the required response for this data. Well, specify the exact version of MySQL.

Akina2021-06-21 04:49:20

Need a search. Yes, an array of rows arrives on the server, accordingly, I can find out the version of MySQL 8.0 adds SQL requests

XtReLL2021-06-21 05:40:14
  • Answer # 1
    Select Activity.id
    From Activity
    Cross Join JSON_Table (@Criteria,
                          '$ [*]' Columns (Keyname Varchar (255) Path '$')) jsontable
    Group by activity.id.
    Having! SUM (JSON_EXTRACT (Activity.comPetencies, Concat ('$.', JSONTABLE.KEYNAME)) IS NULL)
    

    Required filter set is transmitted as a JSON array (I have in a variable @Criteria. ).

    Search is a case-independent (see the last block of code). What is not surprising, for JSON has a binary collation.

  • Answer # 2
    Select Activity.id
    From Activity
    Cross Join JSON_Table (@Criteria,
                          '$ [*]' Columns (Keyname Varchar (255) Path '$')) jsontable
    Group by activity.id.
    Having! SUM (JSON_EXTRACT (Activity.comPetencies, Concat ('$.', JSONTABLE.KEYNAME)) IS NULL)
    

    Required filter set is transmitted as a JSON array (I have in a variable @Criteria. ).

    Search is a case-independent (see the last block of code). What is not surprising, for JSON has a binary collation.