Skip to Content
avatar image
Former Member

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Nov 12, 2015 at 05:14 PM

    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,

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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;