Json in SQL Anywhere16

[Former Member]

Hello All,

I am trying to use Json for complex structure.

I am able to create Json for simple structure with help of Json Raw and Auto but I am not sure how I will be able to implement below complex structure.

Below is the sample Json structure which I am trying to implement.

Another problem is, in our database, User information is storing along with Address in same table. If I will use Json Auto, it won't return data in another array inside User Array.

I also need information about how I can decode same structure back to normal table.

{

  "request": {

    "urgent":                  "boolean",

    "form_id":                 "string",

    "state":                   "STATE",

    "memo":                    "string",

    "User": {

      "first_name":            "required string",

      "middle_name":           "string",

      "last_name":             "required string",

      "date_of_birth":         "date",

      "gender":                "GENDER",

      "email":                 "EMAIL",

      "member_id":             "string",

      "pbm_member_id":         "string",

      "phone_number":          "string",

      "address": {

        "street_1":            "string",

        "street_2":            "string",

        "city":                "string",

        "state":               "STATE",

        "zip":                 "zip"

      }

    }

}

Thanks in advance.

[Former Member · Accepted solution]

Hello Anal,

I believe you have opened a support incident for this question and I have been working directly with you.

I have replied via the incident on how to write query to convert table to complex JSON structure and converting complex JSON structure to table as per your requirement. 

Thank you,

[Former Member · Accepted solution]

Yes Chang... Thanks for the help and query...

Below is the whole script starting from creating Json with inner Array and Parsing back to normal table.

-----------------------------------------------------------------------

// Create Table

CREATE TABLE "User" (

"id" NUMERIC(10,0) NOT NULL,

"lastname" VARCHAR(50) NULL,

"firstname" VARCHAR(50) NULL,

"address" VARCHAR(100) NULL,

"zip" VARCHAR(9) NULL, PRIMARY KEY ( "id" ASC )

) IN "system";

// Insert Data

Insert into "User" values (1,'John','Jony','Street 10, Andrew

Corner','12345');

Insert into "User" values (2,'Jason','Jack','Street 50, Moon

Corner','56456');

Insert into "User" values (3,'John','Jony','Street 20, Mars

Corner','56878');

Insert into "User" values (4,'Jill','Jill','Street 90, Rors

Corner','12345');

// Parse data to Json format

select DISTINCT "User"."firstname", "User"."lastname",

"address"."address" as street_1, "address"."zip" from dba."User" as

"User",

(select DISTINCT "User"."address", "User".firstname, "User"."zip" from

dba."User" as "User", dba."User" as User2 where "User".firstname =

User2.firstname and

"User".id != User2.id GROUP BY "User"."address", "User"."firstname",

"User"."lastname", "User"."zip") AS "address"

WHERE "User"."firstname" = "address"."firstname" ORDER BY 1 FOR JSON

AUTO;

// Alternate way by using Json Explicit

http://dcx.sybase.com/sa160/fr/dbusage/for-json-explicit.html

// Parse data back to normal table

BEGIN

    -- Create variable and table

    CREATE OR REPLACE VARIABLE json_data LONG VARCHAR;

    CREATE TABLE IF NOT EXISTS tdata (FirstName long varchar, LastName

long varchar, street_1 long varchar, zip long varchar);

    DELETE FROM tdata;

    -- Sample data

    SET json_data =

'[{"User":{"firstname":"Jony","lastname":"John","address":[{"street_1":"Street 10, Andrew Corner","zip":"12345"},{"street_1":"Street 20,

MarsCorner","zip":"56878"}]}}] ';

    -- Parse JSON

    CALL sp_parse_json('sarray',json_data);

    -- Insert into table

    INSERT INTO tdata (FirstName, LastName, street_1, zip)

    SELECT

        sarray[[1]]."User".firstname,

        sarray[[1]]."User".lastname,

        sarray[[1]]."User".address[[row_num]].street_1,

        sarray[[1]]."User".address[[row_num]].zip

    FROM sa_rowgenerator(1,CARDINALITY(sarray[[1]]."User".address));

    SELECT * FROM tdata;

END;