Retrieving a Row, with Data from Key-Value Pair Table in MySQL

Retrieving a row, with data from key-value pair table in MySQL

Try this:

SELECT `customer`.*, `ca1`.`value1` AS `wedding_date`, `ca2`.`value1` AS `test` 
FROM `customer` 
LEFT JOIN `customer_attributes` AS `ca1` ON customer.customerID = ca1.customerID  AND ca1.key1='wedding_date'
LEFT JOIN `customer_attributes` AS `ca2` ON customer.customerID = ca2.customerID AND ca2.key1='test'
WHERE (customer.customerID = '58029') 

Moving the 2 WHERE conditions on ca1/ca2 into the JOIN condition instead should sort it

MySQL Column to Key-Value Pair Rows

The most recent versions of MySQL support lateral joins. You could express this as:

select x.*
from wp_users u cross join lateral
     (select 'id' as key, u.id as value union all
      select 'login' as key, u.login as value union all
      select 'nickname' as key, u.nickname as value
     ) x;

You can generate the code for the columns using a spreadsheet or another SQL query, if that is cumbersome.

This also has the advantage of reading the original table only once.

That said, you probably want another column in the result set to identify the entity (the structure you want is probably an entity-attribute-value structure).

How do I return data as a Key & Value pair from MySQL

If you use PDO to connect the database, you can use something like...

$query = $db->query("SELECT Email, ProfilePicture FROM users");
$data  = $query->fetchAll(PDO::FETCH_KEY_PAIR);
$out = json_encode($data);

The PDO::FETCH_KEY_PAIR uses the first column returned as the key and the second column as the value.

Sticking to mysqli

$result = $db->query("SELECT Email, ProfilePicture FROM users");
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[$row['Email']] = $row['ProfilePicture'];
}
$out = json_encode($data);

MySQLi version - slightly shorter...

$result = $db->query("SELECT Email, ProfilePicture FROM users");
$data = $result->fetch_all(MYSQLI_ASSOC);
$out = array_column($data, 'ProfilePicture', 'Email');
$out = json_encode($data);

SELECT .. WHERE .. AND .. (two conditions) for a key-value table?

Until @juergen-d fixes his typo: here is the corrected version:

SELECT commentId 
FROM comment_datas  
GROUP BY commentId
HAVING sum(name = 'type' AND value = 'post') > 0 
   AND sum(name = 'target' AND value = '2') > 0

Explanation:

sum accepts a aggregate expression besides column names so the idea is to sum the entries with name = type and value = post
By having the sum to be greater than 0 the select returns only the rows of the group by that satisfy the aggregate expression - same goes for target.

How to query a table with JSON column with key-value pairs to match all keys and values

This is a type of relational division (with remainder) question, with the extra twist of shredding JSON at the same time.

There are a number of solutions to this type of question. One common solution is to LEFT JOIN the divisor to the dividend, group it and check for any non-matches:

DECLARE @tmp TABLE (
  "Key" NVARCHAR(8) COLLATE Latin1_General_BIN2,
  "Value" NVARCHAR(4) COLLATE Latin1_General_BIN2
);

INSERT INTO @tmp
  ("Key", "Value")
VALUES
  ('size', '200'),
  ('optimize', 'true');
  
SELECT *
FROM Image i
WHERE EXISTS (SELECT 1
    FROM @tmp t
    LEFT JOIN OPENJSON(i.Meta) j ON t.[Key] = j.[key] AND t.Value = j.value
    HAVING COUNT(j.value) = COUNT(*)  -- all match
);

Another solution is to use a double NOT EXISTS: there are no key/value input pairs which do not have a match

DECLARE @tmp TABLE (
  "Key" NVARCHAR(8) COLLATE Latin1_General_BIN2,
  "Value" NVARCHAR(4) COLLATE Latin1_General_BIN2
);

INSERT INTO @tmp
  ("Key", "Value")
VALUES
  ('size', '200'),
  ('optimize', 'true');
  
SELECT *
FROM Image i
WHERE NOT EXISTS (SELECT 1
    FROM @tmp t
    WHERE NOT EXISTS (SELECT 1
        FROM OPENJSON(i.Meta) j
        WHERE t.[Key] = j.[key] AND t.Value = j.value
    )
);

db<>fiddle

YMMV as to which solution is faster.

MySQL table with key/value pairs, get keys as column names

Try this

SELECT
    MAX(CASE WHEN name = 'start' THEN value END) AS `start`,
    MAX(CASE WHEN name = 'stop' THEN value END) AS `stop`,
    MAX(CASE WHEN name = 'fwd' THEN value END) AS `fwd`
FROM info
WHERE id = 110506;

Find row in table depending on JSON-Object key-value

You need to use JSON_TABLE() for this:

mysql> select j.* from users cross join
    -> json_table(users.actions, '$.tasks[*]' columns ( type varchar(10) path '$.type', value varchar(20) path '$.value')) as j 
    -> where type='run' and value like '%*%*%';
+------+-----------+
| type | value     |
+------+-----------+
| run  | a*c*47I*x |
+------+-----------+

If you're still using MySQL 5.x, then the best option is to store the data in normal rows and columns if you need to search in this way. That is, don't store the data as JSON.

In fact, that's the best option even if you do have MySQL 8.0. Isn't the following simpler?

select user_id from tasks
where type='run' and value like '%*%*%';

---

Related Topics