Combine two MySQL select statements together

Question

I have a small table in MySQL 8 with the following schema:

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payload` json NOT NULL,
  `type_id` int(11) NOT NULL,
  `market_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) 

and some data

The type_id=7 has always a referenceId a value that is equal to some market_id.

I am getting my results in two steps

select json_extract(payload, '$.referenceId') from new_table where type_id=7

that returns the market_ids that are stored in a payload (10000)

and after that

select id from new_table where market_id=10000 and type_id=2

that finally gives me all the ids that have type_id=2 and the market_id is referred in some other record.

I would like to do this in one and not two steps. How can I do this? I want all the market_ids of type_id 2, where the market_id is in the json payload where the market_id is 7

Here are some values:

INSERT INTO (`id`,`payload`,`type_id`,`market_id`) VALUES (1,'{\"Value\": 20}',2,10000); INSERT INTO (id,payload,type_id,market_id) VALUES (2,'{\"Value\": 20}',3,10001); INSERT INTO (`id`,`payload`,`type_id`,`market_id`) VALUES (3,'{\"Value\": 30}',4,10002); INSERT INTO (id,payload,type_id,market_id) VALUES (4,'{\"Value\": 120}',12,10003); INSERT INTO (`id`,`payload`,`type_id`,`market_id`) VALUES (5,'{\"Value\": 201}',2,10004); INSERT INTO (id,payload,type_id,market_id) VALUES (6,'{\"Value\": 20, \"referenceId\": 10000}',7,10005); INSERT INTO (`id`,`payload`,`type_id`,`market_id`) VALUES (7,'{\"Value\": 201}',3,10000); INSERT INTO (id,payload,type_id,market_id) VALUES (8,'{\"Value\": 20, \"referenceId\": 10004}',7,10008);

Answer 1

I would like to do this in one and not two steps.

You can do that with a subquery.

select
    id
from
    new_table
where 
    market_id = (
        select json_extract(payload, '$.referenceId')
        from   new_table
        where  type_id = 7
    )
    and type_id = 2

See DB Fiddle for an interactive demo: https://www.db-fiddle.com/f/qHjmGSzwCgRSytW4wuhK1N/0