SQL Dataset / Encode JSON

 I'm looking for creative ways in which to encode a sql dataset to Json with Automate version 11. 

I'm coming up empty at this point. Any suggestions would be most appreciated. 

Thanks.
Chris.

Chris,

Can you be more specific as to what you are looking to encode.

Thanks,

Chris

Why not just use the "JSON encode" action? :)

@Chris:

I'm dealing with product information. I have 4 db tables: Division, Department, Class, Items. DivisionID is the key for Department, DepartmentID is the key for Class and Division, Department and class keys for items.

I need to dynamically produce a similar format as below from these SQL tables. It would be great if I could simply "ConverTo" Json a SQL query to produce this output.

        {
        "name": "MEN'S SPORTSWEAR",
        "category_id": "1",
        "subcategories": [
        {
        "name": "MENS OUTERWEAR",
        "category_id": "100",
        "subcategories": [
                 {
                "name": "LW JACKET",
                "category_id": "1001",
                "children": [
                    {
                    "id": "3626553",
                    "name": "GOLF SOLID JACKET"
                    },
                    {
                    "id": "3470879",
                    "name": "FINE BEDFORD CORD BLAZER"
                    },
                    {
                    "id": "3470903",
                    "name": "FINE BEDFORD CORD BLAZER"
                    },
                    {
                    "id": "3439403",
                    "name": "DBL CHEST STRIPE"
                    },
                    {
                    "id": "3439312",
                    "name": "DBL CHEST STRIPE"
                    },
                    {
                    "id": "3531068",
                    "name": "DBL CHEST STRIPE"
                    },
                    {
                    "id": "3531076",
                    "name": "DBL CHEST STRIPE"
                    },
                    {
                    "id": "3581295",
                    "name": "CHEST PIECED CROSS BOMBER"
                    },
                    {
                    "id": "3586963",
                    "name": "HOODED PARKA"
                    },
                    {
                    "id": "3628286",
                    "name": "INTERNATIONAL NEWPORT VST"
                   }
                 ]
               },

@Mats:

I'm fully aware of the JSON encode action but it's honestly not clear how to implement based on the one sample task I've seen so far. There appears to be very limited forum posts as well on how to implement encode.

Hi Chris,

Please look at the following URL:

https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017

This gives an example on how to output a SQL statement(s) to JSON output. In this example, the dataset that Automate will return from the query will be in JSON format.

-Leonard