0

I have a simple webpage with an input question form. In this form an SQL-query is entered. This query is submitted to a SQL stored procedure and returns a result set in JSON format. F.e. [{"EmployeeID":1,"name":"James"},{"EmployeeID":2,"name":"John"}]. As the queries can change so can the column names and records of the query. I now display the resulting JSON-file as object:

{result.jsonResult}

I would like to display the result in a table format to screen.

EmployeeID name
1 James
2 John

How can I convert this dynamic JSON into a table and display this on the webpage?

import Head from "next/head";
import { useState } from "react";
import styles from "./index.module.css";
import React from "react";

export default function Home() {
  const [queryInput, setQueryInput] = useState("");
  const [result, setResult] = useState({});
  const [jsonResult, setJsonResult] = useState({});

  async function onSubmit(event) {
    event.preventDefault();
    try {
      const response = await fetch("/api/generateTSQL", {
        method: "POST",
        headers: {
          "Content-Type": "application/json",
        },
        body: JSON.stringify({ query: queryInput }),
      });

      const data = await response.json();
      if (response.status !== 200) {
        throw data.error || new Error(`Request failed with status ${response.status}`);
      }

      setResult(data);
      setJsonResult(data.jsonResult);
      setQueryInput("");
    } catch(error) {
      // Consider implementing your own error handling logic here
      console.error(error);
      alert(error.message);
    }
  }

  return (
    <div>
      <Head>
      </Head>
      <main className={styles.main}>
        <form onSubmit={onSubmit}>
          <input
            type="text"
            name="query"
            placeholder="Enter an question"
            value={queryInput}
            onChange={(e) => setQueryInput(e.target.value)}
          />
          <input type="submit" value="Get Query" />
        </form>
        <div className={styles.result}>{result.result}</div>
        <div className={styles.result}>{result.jsonResult}</div>  
      </main>
    </div>
  );
Cees Meuleman
  • 1

1 Answers1

0

To display the JSON object as a table on a webpage, you need to use a JavaScript library such as jQuery, D3.js, or DataTables to parse the JSON data and generate the HTML table.

Here's an example using jQuery and DataTables library:

        $.ajax({
       url: '/your-endpoint-to-retrieve-json-data',
       type: 'GET',
       dataType: 'json',
       success: function(data) {
          $('#your-table-id').DataTable({
             data: data,
             columns: [
                { title: "column1", data: "property1" },
                { title: "column2", data: "property2" },
                // add additional columns as needed
             ]
          });
       }
    });
卄卂丂丂卂几
  • 11
  • 2
  • Thanks for your awnser. However I do not get the json data from an url, but as an output of a SP I am calling. The script is developed in node.js if that helps? – Cees Meuleman Feb 04 '23 at 16:42