Filter out unwanted fields from each JSON object of JSON array while reading from MySQL

Question

Lets say that I have a table in my MySQL database with one of the columns of type JSON and I have saved the following JSON in one of the records

{
  "about": "person",
  "info": [
    {
      "fName": "John",
      "lName": "Booker",
      "sex": "male",
      "age": 20
    },
    {
      "fName": "Laurie",
      "lName": "Sparks",
      "sex": "female"
    },
    {
      "fName": "Adam",
      "lName": "Bate",
      "age": 26
    }
  ]
}

Is there any way that I could extract just the following?

[
  {
    "sex": "male",
    "age": 20
  },
  {
    "sex": "female"
  },
  {
    "age": 26
  }
]

$.info[*] gives me this

[
  {
    "fName": "John",
    "lName": "Booker",
    "sex": "male",
    "age": 20
  },
  {
    "fName": "Laurie",
    "lName": "Sparks",
    "sex": "female"
  },
  {
    "fName": "Adam",
    "lName": "Bate",
    "age": 26
  }
]

and $.info[*].sex and $.info[*].age gives me these respectively

["male", "female"]

[20, 26]

I think I could also work by getting the following but I don't know how to

["male", "female", null]

[20, null, 26]

Original Problem Context

The application I currently work with saves some JSON content in MySQL database. This JSON may have certain fields whose values are huge Base64 encoded images. There are times when my client is not concerned about getting these Base64 encoded images. Thus in order to improve the performance, reduce about of data transferred from database to my application and avoid processing on the application side, I want to retrieve all fields in each JSON object element of an JSON array that doesn't involve storing these huge image information.

My current implementation is to get the entire JSON object from database and then grab the necessary fields using JSON Schema (inspired by this project) but its performance doesn't meet the expected SLA. I really want to do as much data processing I can on MySQL server before getting it into my application and further processing it if there is a need to.

PS: I understand that; unlike something like AWS S3; database may not be the best option for storing large JSON documents. But I want to exploit the batch reads capability of database which may not be available with other NoSQL datastores

Answer 1

Update

As of MySQL 8.0 you can do this more easily using JSON_TABLE to extract the sex and age values from each object:

SELECT JSON_ARRAYAGG(JSON_OBJECT('sex', j.sex, 'age', j.age)) AS filtered
FROM test t
JOIN JSON_TABLE(t.j, 
                '$.info[*]'
                COLUMNS (
                  sex VARCHAR(6) PATH '$.sex',
                  age INT PATH '$.age'
                )
                ) j

Assuming a column j with your original data in it, this gives:

[
  {"age": 20, "sex": "male"},
  {"age": null, "sex": "female"},
  {"age": 26, "sex": null}
]

If you want to avoid the null values, you can use a CASE expression to check for them and change the object created based on that:

SELECT JSON_ARRAYAGG(
         CASE WHEN j.sex IS NULL THEN JSON_OBJECT('age', j.age)
              WHEN j.age IS NULL THEN JSON_OBJECT('sex', j.sex)
              ELSE JSON_OBJECT('sex', j.sex, 'age', j.age)
         END
         ) AS filtered
FROM test t
JOIN JSON_TABLE(t.j, 
                '$.info[*]'
                COLUMNS (
                  sex VARCHAR(6) PATH '$.sex',
                  age INT PATH '$.age'
                )
                ) j
WHERE j.sex IS NOT NULL OR j.age IS NOT NULL

Output:

[
  {"age": 20, "sex": "male"},
  {"sex": "female"},
  {"age": 26}
]

Demo (of both queries) on dbfiddle

Original Answer

Unfortunately I don't think this can be done directly with the inbuilt MySQL JSON functions (if JSON_REMOVE would accept wild card paths it might be). However it can be achieved with a stored function like this:

CREATE FUNCTION filter_json(j JSON) RETURNS JSON
BEGIN
  DECLARE n INT DEFAULT 0;
  DECLARE info, sex, age, data JSON;

  SET info = JSON_ARRAY();
  WHILE JSON_CONTAINS_PATH(j, 'one', CONCAT('$.info[', n, ']')) DO
    SET data = JSON_OBJECT();
    SET sex = JSON_EXTRACT(j, CONCAT('$.info[', n, '].sex'));
    IF sex IS NOT NULL THEN
       SET data = JSON_INSERT(data, '$.sex', sex);
    END IF;
    SET age = JSON_EXTRACT(j, CONCAT('$.info[', n, '].age'));
    IF age IS NOT NULL THEN
       SET data = JSON_INSERT(data, '$.age', age);
    END IF;
    SET info  = JSON_ARRAY_APPEND(info, '$', data);
    SET n = n + 1;
  END WHILE;
  RETURN info;
END;

Note that dependent on your environment you may need to change the delimiter (e.g. using DELIMITER //) before entering the function.

For this query:

SELECT filter_json('{ "about": "person", "info": [ { "fName": "John", "lName": "Booker", "sex": "male", "age": 20 }, { "fName": "Laurie", "lName": "Sparks", "sex": "female" }, { "fName": "Adam", "lName": "Bate", "age": 26 } ] }')

You will get your desired output:

[
  {"age": 20, "sex": "male"},
  {"sex": "female"}, 
  {"age": 26}
]

Demo on dbfiddle