0

Let's take a simple schema with two tables, one that describes an simple entity item (id, name) 

id  |   name
------------
1   |   foo
2   |   bar

and another, lets call it collection, that references to an item, but inside a JSON Object in something like

{
    items: [
        {
            id: 1,
            quantity: 2
        }
    ]
}

I'm looking for a way to eventually enrich this field (kind of like populate in Mongo) in the collection with the item element referenced, to retrieve something like

{
    ...
    items: [
        {
            item: {
                id: 1,
                name: foo
            },
            quantity: 2
        }
    ]
    ...
}

If you have a solution with PostgreSQL, I take it as well.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Yohan Courbe
  • 25
  • 5
  • Have you already seen [this post](https://stackoverflow.com/questions/3766282/fill-database-tables-with-a-large-amount-of-test-data)? – Kate Orlova Jul 25 '19 at 22:14
  • You should read about the [Inner-platform Effect](https://en.wikipedia.org/wiki/Inner-platform_effect). – Bill Karwin Jul 25 '19 at 23:56

1 Answers1

1

If I understood correctly, your requirement is to convert an Input JSON data into MySQL table so that you can work with JSON but leverage the power of SQL.

Mysql8 recently released JSONTABLE function. By using this function, you can store your JSON in the table directly and then query it like any other SQL query.

It should serve your immediate case, but this means that your table schema will have a JSON column instead of traditional MySQL columns. You will need to check if it serves your purpose.

This is a good tutorial for the same.

Abhishek Garg
  • 2,158
  • 1
  • 16
  • 30