MySQL can return JSON data. The JSON_EXTRACT function extracts field values. For complex queries, consider using the WHERE clause to filter JSON data, but pay attention to its performance impact. MySQL's support for JSON is constantly increasing, and it is recommended to pay attention to the latest version and features.
Can MySQL return JSON? The answer is: Yes, but it depends on how you ask.
This question seems simple, but it actually has a secret. On the surface, you might just want to get a column of data from the MySQL database, which happens to be a string in JSON format. Of course, this is no problem. You can do it with a normal SELECT statement, just like you take any other type of data. But if your needs are more complicated, such as using SQL statements to directly manipulate fields in JSON data, things will become much more interesting.
Let's start with the most basic ones. Suppose you have a table called products , which has a details column, which stores the JSON data of the product:
<code class="sql">CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(255), details JSON ); INSERT INTO products (id, name, details) VALUES (1, '手機(jī)', '{"brand": "Apple", "model": "iPhone 14", "price": 7999}'), (2, '電腦', '{"brand": "Dell", "model": "XPS 13", "price": 12999}');</code>
Now that you want to remove all the brands of products, you can write this:
<code class="sql">SELECT id, name, JSON_EXTRACT(details, '$.brand') AS brand FROM products;</code>
The JSON_EXTRACT function is a tool provided by MySQL to extract specific fields from JSON data. This is very simple, right? But you may encounter some pitfalls. For example, if some JSON data in details column is incorrect in the format, or a certain field does not exist, JSON_EXTRACT may return NULL . You need to be careful to handle these exceptions and don't let your program crash because of bad data. You can use the JSON_VALID function to check the validity of JSON data first.
Going further, if you want to filter JSON data directly with SQL, such as finding all computers that cost more than 10,000, you can do this:
<code class="sql">SELECT * FROM products WHERE JSON_EXTRACT(details, '$.price') > 10000;</code>
This looks elegant, but in reality, the performance of this approach may not be ideal, especially when the data volume is high. When MySQL's JSON function processes a large amount of data, it may not be as efficient as filtering directly with fields from a relational database. Therefore, when designing a database, you need to weigh the pros and cons. If your JSON data structure is relatively simple, and you mainly need full-text retrieval or some simple field extraction, then using the JSON type may be a good choice. However, if your JSON data is very complex and requires frequent complex queries and updates, you may need to rethink your database design, and perhaps splitting the JSON data into multiple relational fields is more efficient.
Finally, I would like to remind you that MySQL's support for JSON is constantly evolving. The new version of MySQL provides more and more powerful JSON functions to more conveniently manipulate JSON data. Therefore, always pay attention to the updates of MySQL and learn new functions to write more efficient and elegant code. Remember, only by choosing the right tool and mastering its advantages and disadvantages can you become a real programming master.
The above is the detailed content of Can mysql return json. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
The popularity of the currency circle has returned, why do smart people have begun to quietly increase their positions? Look at the trend from the on-chain data and grasp the next round of wealth password!
Jul 09, 2025 pm 08:30 PM
As the market conditions pick up, more and more smart investors have begun to quietly increase their positions in the currency circle. Many people are wondering what makes them take decisively when most people wait and see? This article will analyze current trends through on-chain data to help readers understand the logic of smart funds, so as to better grasp the next round of potential wealth growth opportunities.
Bitcoin breaks new highs, Dogecoin rebounds strongly, will Ethereum keep up with the pace
Jul 09, 2025 pm 08:24 PM
Recently, Bitcoin hit a new high, Dogecoin ushered in a strong rebound and the market was hot. Next, we will analyze the market drivers and technical aspects to determine whether Ethereum still has opportunities to follow the rise.
Who issues stablecoins? What are the stablecoins?
Jul 09, 2025 pm 06:24 PM
Stablecoins are crypto assets that maintain price stability by anchoring fiat currencies such as the US dollar. They are mainly divided into three categories: fiat currency collateral, crypto asset collateral and algorithmic stablecoins. 1. USDT is issued by Tether and is the stablecoin with the largest market value and the highest liquidity. 2. USDC is released by the Centre alliance launched by Circle and Coinbase, and is known for its transparency and compliance. 3. DAI is generated by MakerDAO through over-collateralization of crypto assets and is the core currency in the DeFi field. 4. BUSD was launched in partnership with Paxos, and is regulated by the United States but has been discontinued. 5. TUSD achieves high transparency reserve verification through third-party escrow accounts. Users can use centralized exchanges such as Binance, Ouyi, and Huobi
What is a stablecoin? Why are the hot searches on Douyin discussed?
Jul 09, 2025 am 06:03 AM
Stablecoins are cryptocurrencies that are pegged to assets such as the US dollar and aim to maintain stable value. They are mainly divided into three types: fiat currency collateral, cryptocurrency collateral and algorithms. 1. Fiat currency collateral types such as USDT and USCD are supported by US dollar reserves; 2. Cryptocurrency collateral types such as DAI need to over-collateralize other currencies; 3. Algorithm relies on smart contracts to adjust supply but have high risks. The reasons why it is hotly discussed on platforms such as Douyin include: as a hedging tool when the crypto market falls, a bridge for novices to enter the crypto world, a way to obtain high-yield financial management in DeFi, and the application of low-cost cross-border payments. To obtain stablecoins, you can trade through mainstream exchanges such as Binance, Ouyi, and Huobi.
The top 5 most worth investing in 2025 (with latest data)
Jul 09, 2025 am 06:06 AM
The five most valuable stablecoins in 2025 are Tether (USDT), USD Coin (USDC), Dai (DAI), First Digital USD (FDUSD) and TrueUSD (TUSD).
Top ten stablecoin leading stocks
Jul 09, 2025 am 06:00 AM
As an important cornerstone of the crypto world, stablecoins provide the market with value anchoring and hedging functions. This article lists the top ten stablecoin projects with current market value and influence: 1. Tether (USDT) has become a market leader with its extensive liquidity and trading depth; 2. USD Coin (USDC) is known for its compliance and transparency, and is the first choice for institutional investors; 3. Dai (DAI) is the core of decentralized stablecoin, generated by the MakerDAO protocol; 4. First Digital USD (FDUSD) has risen rapidly due to Binance support; 5. TrueUSD (TUSD) emphasizes transparency in third-party audits; 6. Frax (FRAX) adopts collateral
Cardano's smart contract evolution: The impact of Alonzo upgrades on 2025
Jul 10, 2025 pm 07:36 PM
Cardano's Alonzo hard fork upgrade has successfully transformed Cardano from a value transfer network to a fully functional smart contract platform by introducing the Plutus smart contract platform. 1. Plutus is based on Haskell language, with powerful functionality, enhanced security and predictable cost model; 2. After the upgrade, dApps deployment is accelerated, the developer community is expanded, and the DeFi and NFT ecosystems are developing rapidly; 3. Looking ahead to 2025, the Cardano ecosystem will be more mature and diverse. Combined with the improvement of scalability in the Basho era, the enhancement of cross-chain interoperability, the evolution of decentralized governance in the Voltaire era, and the promotion of mainstream adoption by enterprise-level applications, Cardano has
What are the mainstream public chains of cryptocurrencies? The top ten rankings of cryptocurrency mainstream public chains in 2025
Jul 10, 2025 pm 08:21 PM
The pattern in the public chain field shows a trend of "one super, many strong ones, and a hundred flowers blooming". Ethereum is still leading with its ecological moat, while Solana, Avalanche and others are challenging performance. Meanwhile, Polkadot, Cosmos, which focuses on interoperability, and Chainlink, which is a critical infrastructure, form a future picture of multiple chains coexisting. For users and developers, choosing which platform is no longer a single choice, but requires a trade-off between performance, cost, security and ecological maturity based on specific needs.
