B4J Question Export B4XTable to JSON

[Brian Michael]

Hi, i have this code created by @Erel, i edit some lines.

Public Sub ExportTableToJSON (Table As B4XTable)
    
    Dim data As List
    data.Initialize
    
    Dim rs As ResultSet = Table.sql1.ExecQuery("SELECT * FROM data")
    
    Do While rs.NextRow
        Dim row As List
        row.Initialize
        Dim c As B4XTableColumn
        For i = 0 To Table.Columns.Size - 1
            c = Table.Columns.Get(i)
            Select c.ColumnType
                Case Table.COLUMN_TYPE_DATE
                    row.Add(c.Id & $""$ & ":" & $""$ & rs.GetLong(c.SQLID))
                Case Table.COLUMN_TYPE_NUMBERS
                    row.Add(c.Id & $""$ & ":" & $""$ & rs.GetDouble(c.SQLID))
                Case Table.COLUMN_TYPE_TEXT
                    row.Add(c.Id & $""$ & ":" & $""$ & rs.GetString(c.SQLID))
            End Select
        Next
        data.Add(row)
    Loop
    
    rs.Close
    
    Dim Headers As List
    Headers.Initialize
    For Each c As B4XTableColumn In Table.Columns
        Headers.Add(c.Id)
    Next
    Dim jg As JSONGenerator
    jg.Initialize(CreateMap("Data": data))
    
    'File.WriteString(Dir, FileName, jg.ToPrettyString(4))
    Log(jg.ToPrettyString(4))
End Sub

I get this output:

{
    "Data": [
        [
            "COD_PRODUCTO:1",
            "PRODUCTO:PRODUCTO PRUEBA 1",
            "UND:10",
            "COD_MARCA:111",
            "MARCA:PRUEBA",
            "PROVEEDOR:PRUEBAS",
            "CATEGORIA:PRODUCTOS",
            "IMAGE_LINK:http:\/\/www.directimage.com\/1.png"
        ],
        [
            "COD_PRODUCTO:2",
            "PRODUCTO:PRODUCTO PRUEBA 2",
            "UND:5",
            "COD_MARCA:222",
            "MARCA:PRUEBA",
            "PROVEEDOR:PRUEBAS",
            "CATEGORIA:PRODUCTOS",
            "IMAGE_LINK:http:\/\/www.directimage.com\/1.png"
        ]
    ]
}

But it doesn't have the JSON structure I need, i want to get some like:

{
    "Data": [
        [
            "COD_PRODUCTO":"1",
            "PRODUCTO":"PRODUCTO PRUEBA 1",
            "UND":"10",
            "COD_MARCA":"111",
            "MARCA":"PRUEBA",
            "PROVEEDOR":"PRUEBAS",
            "CATEGORIA":"PRODUCTOS",
            "IMAGE_LINK":"http://www.directimage.com/1.png"
        ],
        [
            "COD_PRODUCTO":"2",
            "PRODUCTO":"PRODUCTO PRUEBA 2",
            "UND":"5",
            "COD_MARCA":"222",
            "MARCA":"PRUEBA",
            "PROVEEDOR":"PRUEBAS",
            "CATEGORIA":"PRODUCTOS",
            "IMAGE_LINK":"http://www.directimage.com/1.png"
        ]
    ]
}

 

[Brian Michael]

I get the solution:

I just create a Map var to set the data, before i add this map in the list.

    Do While rs.NextRow
        Dim row As List
        row.Initialize
        
        Dim trow As Map
        trow.Initialize
        
        Dim c As B4XTableColumn
        For i = 0 To Table.Columns.Size - 1
            c = Table.Columns.Get(i)
            Select c.ColumnType
                Case Table.COLUMN_TYPE_DATE
                    trow.Put(c.Id,rs.GetLong(c.SQLID))
                Case Table.COLUMN_TYPE_NUMBERS
                    trow.Put(c.Id,rs.GetDouble(c.SQLID))
                Case Table.COLUMN_TYPE_TEXT
                    trow.Put(c.Id,rs.GetString(c.SQLID))
            End Select
        Next
        data.Add(trow)
    Loop