How to Convert a JSON String into an SQL Query — SitePoint

In this article, we’ll walk through the process of converting a JSON string into an SQL Query so that your data can be inserted into a database.

Anatomy of JSON Objects and SQL Queries

Here’s an example of a JSON object:

<code class="javascript language-javascript"><span class="token punctuation">{</span>
<span class="token string">"name"</span><span class="token operator">:</span> <span class="token string">"John"</span><span class="token punctuation">,</span>
<span class="token string">"age"</span><span class="token operator">:</span> <span class="token number">30</span><span class="token punctuation">,</span>
<span class="token string">"city"</span><span class="token operator">:</span> <span class="token string">"New York"</span>
<span class="token punctuation">}</span>
</code>

In the code above, we have a JSON object with three attributes: name, age, and city. Each attribute has a corresponding value.

Here’s an example of an SQL query:

<code class="sql language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> users <span class="token keyword">WHERE</span> age <span class="token operator">></span> <span class="token number">18</span><span class="token punctuation">;</span>
</code>

In this example, we’re selecting all records from the users table where the age is greater than 18.

How to Convert a JSON String to a JSON Object and then into an SQL Query

To convert a string to JSON and then into an SQL query, we need to follow these steps:

Parse the JSON string into a JSON object
Extract the values from the JSON object

Build an SQL query using the extracted values

Let’s go through each step in detail.

Parse the string into a JSON object

To parse the string into a JSON object, we can use the JSON.parse() method. This method takes a string as input and returns a JSON object:

<code class="javascript language-javascript"><span class="token keyword">const</span> jsonString <span class="token operator">=</span> <span class="token string">'{"name":"John","age":30,"city":"New York"}'</span><span class="token punctuation">;</span>
<span class="token keyword">const</span> jsonObj <span class="token operator">=</span> <span class="token known-class-name class-name">JSON</span><span class="token punctuation">.</span><span class="token method function property-access">parse</span><span class="token punctuation">(</span>jsonString<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token console class-name">console</span><span class="token punctuation">.</span><span class="token method function property-access">log</span><span class="token punctuation">(</span>jsonObj<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code>

In this example, we have a JSON string and we’re using the JSON.parse() method to parse it into a JSON object. The output of this code will be as follows:

<code class="javascript language-javascript"><span class="token punctuation">{</span>
<span class="token string">"name"</span><span class="token operator">:</span> <span class="token string">"John"</span><span class="token punctuation">,</span>
<span class="token string">"age"</span><span class="token operator">:</span> <span class="token number">30</span><span class="token punctuation">,</span>
<span class="token string">"city"</span><span class="token operator">:</span> <span class="token string">"New York"</span>
<span class="token punctuation">}</span>
</code>

Once we have the JSON object, we need to extract the values from it. We can do this by accessing the properties of the JSON object like so:

<code class="javascript language-javascript"><span class="token keyword">const</span> name <span class="token operator">=</span> jsonObj<span class="token punctuation">.</span><span class="token property-access">name</span><span class="token punctuation">;</span>
<span class="token keyword">const</span> age <span class="token operator">=</span> jsonObj<span class="token punctuation">.</span><span class="token property-access">age</span><span class="token punctuation">;</span>
<span class="token keyword">const</span> city <span class="token operator">=</span> jsonObj<span class="token punctuation">.</span><span class="token property-access">city</span><span class="token punctuation">;</span>
<span class="token console class-name">console</span><span class="token punctuation">.</span><span class="token method function property-access">log</span><span class="token punctuation">(</span>name<span class="token punctuation">,</span> age<span class="token punctuation">,</span> city<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code>

In this example, we’re extracting the values of the name, age, and city properties from the JSON object. The output of this code will be as follows:

<code class="javascript language-javascript"><span class="token maybe-class-name">John</span> <span class="token number">30</span> <span class="token maybe-class-name">New</span> <span class="token maybe-class-name">York</span>
</code>

Now that we’ve extracted the values from the JSON object, we can use them to build an SQL query:

<code class="javascript language-javascript"><span class="token keyword">const</span> sqlQuery <span class="token operator">=</span> <span class="token template-string"><span class="token template-punctuation string">`</span><span class="token string">INSERT INTO users (name, age, city) VALUES ('</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>name<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">', '</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>age<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">', '</span><span class="token interpolation"><span class="token interpolation-punctuation punctuation">${</span>city<span class="token interpolation-punctuation punctuation">}</span></span><span class="token string">')</span><span class="token template-punctuation string">`</span></span><span class="token punctuation">;</span>
<span class="token console class-name">console</span><span class="token punctuation">.</span><span class="token method function property-access">log</span><span class="token punctuation">(</span>sqlQuery<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code>

In this example, we’re building an SQL query to insert a new record into the users table with the values extracted from the JSON object. The output of this code will be as follows:

<code class="javascript language-javascript"><span class="token constant">INSERT</span> <span class="token constant">INTO</span> <span class="token function">users</span> <span class="token punctuation">(</span>name<span class="token punctuation">,</span> age<span class="token punctuation">,</span> city<span class="token punctuation">)</span> <span class="token constant">VALUES</span> <span class="token punctuation">(</span><span class="token string">'John'</span><span class="token punctuation">,</span> <span class="token string">'30'</span><span class="token punctuation">,</span> <span class="token string">'New York'</span><span class="token punctuation">)</span>
</code>

Converting a JSON string into an SQL query is a common task in web development. By following the steps outlined here, you can easily work with JSON data and manipulate it so that it can be inserted into your SQL database.

Source link