Import JSON to Mysql table

Asked

Viewed 363 times

0

I am trying to import a JSON into Mysql, but without success

EDIT: JSON has the structure below, it is return of this wikidata sparql query. The query created a document for each tuple

{
    "_id" : ObjectId("5d02f06243ced42c10bd28dc"),
    "tipoLinguagem" : "http://www.wikidata.org/entity/Q9143",
    "tipoLinguagemLabel" : "programming language",
    "subtipoLinguagem" : "http://www.wikidata.org/entity/Q3839507",
    "subtipoLinguagemLabel" : "functional programming language",
    "linguagem" : "http://www.wikidata.org/entity/Q2005",
    "linguagemLabel" : "JavaScript",
    "wd" : "http://www.wikidata.org/entity/P127",
    "wdLabel" : "owned by",
    "ps" : "http://www.wikidata.org/prop/statement/P127",
    "ps_Label" : "Oracle Corporation",
    "isIdentifier" : "false"
}
{
    "_id" : ObjectId("5d02f06243ced42c10bd28dd"),
    "tipoLinguagem" : "http://www.wikidata.org/entity/Q9143",
    "tipoLinguagemLabel" : "programming language",
    "subtipoLinguagem" : "http://www.wikidata.org/entity/Q1993334",
    "subtipoLinguagemLabel" : "interpreted language",
    "linguagem" : "http://www.wikidata.org/entity/Q2005",
    "linguagemLabel" : "JavaScript",
    "wd" : "http://www.wikidata.org/entity/P127",
    "wdLabel" : "owned by",
    "ps" : "http://www.wikidata.org/prop/statement/P127",
    "ps_Label" : "Sun Microsystems",
    "isIdentifier" : "false"
}
{
    "_id" : ObjectId("5d02f06143ced42c10bd27af"),
    "tipoLinguagem" : "http://www.wikidata.org/entity/Q9143",
    "tipoLinguagemLabel" : "programming language",
    "subtipoLinguagem" : "http://www.wikidata.org/entity/Q3839507",
    "subtipoLinguagemLabel" : "functional programming language",
    "linguagem" : "http://www.wikidata.org/entity/Q2005",
    "linguagemLabel" : "JavaScript",
    "wd" : "http://www.wikidata.org/entity/P571",
    "wdLabel" : "inception",
    "ps" : "http://www.wikidata.org/prop/statement/P571",
    "ps_Label" : "1995-12-04T00:00:00Z",
    "isIdentifier" : "false"
}

What I need is to consult bank to bring objects like:

{
  languageName: 'Javascript',
  paradigms: [
      function, scripting, etc
  ]
  props: [
     owned by: value,
     inception: value
     etc...
  ]
}
E também um objeto para os paradigmas

{
  paradigm: functional programming
  languages: javascript, curl, etc...
}

How should the tables be created? Each example I found uses a different method, and I could not with any of them.

I wanted something as simple as the mogoimport.

**I couldn’t install Workbench on Ubuntu 19.04, either by the terminal or by the store

** Does the entire JSON stand in one column? That seems very wrong...

CREATE TABLE IF NOT EXISTS lings_json( 
    id INT NOT NULL AUTO_INCREMENT,
    json_data JSON NOT NULL, 
    PRIMARY KEY (`id`)
);

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

mysql database json
  • 1

    I think it would be useful to know what is in "Q3.json"

  • I edited there, you can help me?

1 answer

1


Mysql database is not ideal for this solution. What you can do with it is create a json type column (only mysql version 5.7 or higher if I am not mistaken) that serves to store a Json type object (useful for when we have an address type object for example, where we do not know how many parameters will be filled in). But it doesn’t give you features like advanced search to perform in SELECTS. So basically you’re extremely limited when you need to look for things inside a json object. The use we have of json for Mysql is only to bring object, being in charge of the application to unfold that object, (serialize, deserialize). If you want to perform advanced searches and things within a Json, use Mongodb or another bank that has support for it. Or even use the two banks, Mysql to handle relationships by placing a key to a Mongo goal for example.

  • so I started using Mongo. but in the question below I was told that a relational comic https://answall.com/questions/391454/modelar-base-de-data

  • without wanting to abuse, could you help me with the doubts of this other question? i only worked with Oracle for now, I’m having difficulty manipulating the data by Mongo/Mongoose.

  • So the problem is just this, first you have to define, my problem is relational? Is information structured? If the data is structured, you use relational DBMS, Oracle, Mysql, Postgree, etc. But if your data is mostly unstructured, then you’re off to a Mongo of life. Example: I have a given address, however it has several ways, Sometimes it has block, add-on, reference, etc. If you play NULL at all, you lose performance and disk... so we have solutions like Mongo to handle unstructured data.

  • Mongo is extremely simple, it does not have this first key, foreign, relationship or anything it is like a giant text file, however it has the "functions" ready for data selection and search. Depending on the type library the mogoose is extremely simple to make a selection, the syntax is infinitely simpler than that of an SQL. The example you passed the data is structured (has a pattern), so it is possible to use relational DBMS, each object attribute, type Language, type Language, etc... Window turns a column, and inserts the data.

  • Yes, I will have to create several Inserts... I was looking for a solution like the mongoimport, but I will have to leave the sloth aside

Browser other questions tagged mysql database json

You are not signed in. Login or sign up in order to post.