Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Json in SQL Anywhere16

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.

Tags:
Former Member
Former Member replied

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;

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question