Question 109551 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Extract & Display JSON Data from SQL Database Using JavaScript

I have a column (student_no) in my database with JSON data (String).

student No: [
     {First_name: 'John',
      Last_name: 'Wick',
      student_id: 1001,
     },
     {First_name: 'David',
      Last_name: 'Stone',
      student_id: 1002, 
     }

If I want to read and display only the student_id = 1002 from the data, what is the right query?

I tried using student_no.student_id = 1002 but there were error in the syntax. I want to access and display the First_name, Last_name by accessing the student_id.

Tags:

2

Answers


  1. 0

    JSON data cannot be parsed with a SELECTstatement. You’ll need to query your desired columns with SELECT (including the JSON column) but then to use the JSON_EXTRACT function. Check out this link for a good examples of working with JSON data in MySQL.

    Login or Signup to reply.
  2. 0

    With Javascript Code

    You can use Javascript Array Filter (.filter) to solve that problem. Try this :

    function getSingleData(data, student_id) {
  return data.filter(d => d.student_id === student_id)[0];
}

let data = [ {First_name: 'John', Last_name: 'Wick', student_id: 1001, }, {First_name: 'David', Last_name: 'Stone', student_id: 1002, }];
console.log(getSingleData(data, 1002));

    With Maria DB Query

    I assume your table (my_table) is look like this :

    id student_no
    1 [ { "First_name": "John", "Last_name": "Wick", "student_id": 1001 }, { "First_name": "David", "Last_name": "Stone", "student_id": 1002 } ]

    To get the data from column student_no which is an array of objects by student_id, just use the JSON_TABLE query like this :

    SELECT j.*
FROM my_table m, JSON_TABLE(m.student_no, '$[*]' COLUMNS (
  First_name VARCHAR(50) path '$.First_name',
  Last_name VARCHAR(50) path '$.Last_name',
  student_id INT(11) path '$.student_id')) AS j
WHERE j.student_id = 1002;

    The output will be :

    First_name Last_name student_id
    David Stone 1002

    Notes: JSON_TABLE only works starts in MariaDB 10.6.0

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search