I am looking to use a join to replace a reference with a value in the following mysql

the output from the final function should be

{"Fav_Friend": "Ross", "castMember": "Elaine"}
{"Fav_Friend": "Gina", "castMember": "George"}
{"Fav_Friend": "Gina", "castMember": "Jerry"}
{"Fav_Friend": "Monica", "castMember": "Kramer"}
{"Fav_Friend": "Rachel", "castMember": "Numan"}

Note in this function we are changing

the key names -> works ok,    
      name->castmember
      favfriend->Fav_Friend

get last unique row that matches $.name -> works OK 
eg: {"Fav_Friend": "c1fcb509e6b34e5ba5d0a44329a88a94", "castMember": "Elaine"}

WITH lastEntry AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY details->>'$.name' ORDER BY uid DESC) AS lE
  FROM people AS m
  ) SELECT 
      JSON_OBJECT('castMember',details->>'$.name','Fav_Friend',details->>'$.favfriend')
      FROM lastEntry WHERE lE = 1;

looking at other answers on SO I have tried adding a LEFT JOIN CAST but this returns an error

 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN friends g on JSON_CONTAINS(m.details->>'$.favfriend', CAST(g.crew-' at line 6
 
WITH lastEntry AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY details->>'$.name' ORDER BY uid DESC) AS lE
  FROM people AS m
  ) SELECT 
      JSON_OBJECT('castMember',details->>'$.name','Fav_Friend',details->>'$.favfriend')
      FROM lastEntry WHERE lE = 1
  LEFT JOIN friends g on JSON_CONTAINS(m.details->>'$.favfriend', CAST(g.crew->>'$.screen' as JSON), '$');

and also tried direct reference

LEFT JOIN friends g on JSON_CONTAINS(lastEntry.details->>'$.favfriend', CAST(g.crew->>'$.screen' as JSON), '$');

in case this is the wrong direction I also tried variations of

LEFT JOIN friends g on people.details->>'$.favfriend' = g.crew->>'$.screen';

how can I replace the guid with the screen name?

Here is the table data

    CREATE DATABASE castfriends
    DEFAULT CHARACTER SET = 'utf8mb4';

use castfriends;
CREATE TABLE `people` (
  `uid` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `details` json DEFAULT NULL COMMENT 'supporting fields for parent',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb3;

insert into castfriends.people (details)  values 
(
  '{
    "guid" : "c14ad6a7e63a4f6c8d68b4bc3a3dd404",
    "name": "George",
    "favfriend":"062a55f932ea408e8f8f1914f1499a24"
  }'
),
(
  '{
    "guid" : "f4aec5a0043f4933a084cea137ebcff9",
    "name": "Elaine",
    "favfriend":"d8930c49405242d5923362b8818b93ad"
  }'
),
(
  '{
    "guid" : "c788335702eb413496c7d60e7041c34c",
    "name": "Jerry",
    "favfriend":"a846ec1c09844012bb1d3e64ae311bf8"
  }'
),
(
  '{
    "guid" : "30425947838149fda9f836a089605459",
    "name": "Kramer",
    "favfriend":"a846ec1c09844012bb1d3e64ae311bf8"
  }'
),
(
  '{
    "guid" : "18dc6c62c2554ef796c684164d72eee0",
    "name": "Numan",
    "favfriend":"a846ec1c09844012bb1d3e64ae311bf8"
  }'
),
(
  '{
    "guid" : "c15ea8d20f8d4906a1d9704e347e5d81",
    "name": "Elaine",
    "favfriend":"c1fcb509e6b34e5ba5d0a44329a88a94"
  }'
),
(
  '{
    "guid" : "975f1fdc11a64d4ba716513f5d08c23d",
    "name": "Jerry",
    "favfriend":"062a55f932ea408e8f8f1914f1499a24"
  }'
),
(
  '{
    "guid" : "f1fb99a8ac1342c5b266af44dfa92db1",
    "name": "Kramer",
    "favfriend":"49578c5a85a1454597b911e8c6300828"
  }'
),
(
  '{
    "guid" : "feeab20a78bd4297aa8a920c91760d8b",
    "name": "Numan",
    "favfriend":"d8930c49405242d5923362b8818b93ad"
  }'
);
use castfriends;
CREATE TABLE `friends` (
  `uid` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `crew` json DEFAULT NULL COMMENT 'supporting fields for parent',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb3;

insert into castfriends.friends (crew)  values 
(
  '{
    "guid" : "d8930c49405242d5923362b8818b93ad",
    "screen": "Rachel"
  }'
),
(
  '{
    "guid" : "49578c5a85a1454597b911e8c6300828",
    "screen": "Monica"
  }'
),
(
  '{
    "guid" : "a846ec1c09844012bb1d3e64ae311bf8",
    "screen": "Ursula"
  }'
),
(
  '{
    "guid" : "c1fcb509e6b34e5ba5d0a44329a88a94",
    "screen": "Ross"
  }'
),
(
  '{
    "guid" : "062a55f932ea408e8f8f1914f1499a24",
    "screen": "Gina"
  }'
);

CodePudding user response：

You have a WHERE clause before a LEFT JOIN. It needs to come after...

• SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... ORDER BY ...

CodePudding user response：

Parse your JSONs to separate columns:

SELECT people.uid, p_details.*
FROM people
CROSS JOIN JSON_TABLE( people.details,
                       '$' COLUMNS ( guid CHAR(32) PATH '$.guid',
                                     name VARCHAR(255) PATH '$.name',
                                     favfriend CHAR(32) PATH '$.favfriend' 
                                    )
                      ) p_details;

SELECT friends.uid, f_details.*
FROM friends
CROSS JOIN JSON_TABLE( friends.crew ,
                       '$' COLUMNS ( guid CHAR(32) PATH '$.guid',
                                     screen VARCHAR(255) PATH '$.screen'
                                    )
                      ) f_details;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ea67858bd8abae43d73224c818ee58d2

Using these queries as CTEs join them in outer query, apply any needed conditions, then reconstruct desired final JSON values and use in UPDATE.