Currently sp_ parse_json() can only parse to a long varchar. It also has limitations on arrays of objects within the json.

The following json:

{ "OrderId": 1,
  "Lines": [{"Id":1, "Product": "Milk", "Surcharge" : 1.00},
            {"Id":2, "Product": "Water"},
            {"Id":3, "Product": "Oil", "Surcharge" : 2.00}
           ]
}
can not be handled by the sp_ parse_ json() procedure because the objects in the arrays are not equal.

My suggestion would be to use a variable as parameter that corresponds to the format of the json that's expected.

declare variable order_object   ROW( OrderId integer,
                                     ARRAY OF ROW( Id integer,
                                                   Product varchar(64),
                                                   Surcharge numeric(5,2)
                                                 )
                                   );

call sp_parse_json(order_object,'{...}')

The parser should then map the json on the passed variable and for every element that could not be found it could set it to NULL.

It would even be better if you could set the defaults for the variable if missing in the json like columns in a table. Then bytes for example could be defaulted to 0. But that's currently not possible in a ROW() data type.

I don't know where I can ask for these suggestions, so maybe someone can help me out on this.

asked 18 Jun '21, 06:25

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k354564
accept rate: 21%

edited 18 Jun '21, 07:29

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

Currently sp_ parse_json() can only parse to a long varchar.

IMHO that's not really true, you can certainly provide a fitting composite variable - but alas, it's usually is ignored...

I certainly second your suggestion. There are several questions here dealing with missing features for JSON import.

(18 Jun '21, 07:29) Volker Barth
1

I don't know where I can ask for these suggestions

Historically, the added "product-suggestion" tag has been the means to point out such suggestions. However, I don't know how/whether the SQL Anywhere team takes note of these currently... (There used to be wayyyyyy more official feedback, sigh.)

(18 Jun '21, 07:33) Volker Barth
1

> I don't know where I can ask for these suggestions, so maybe someone can help me out on this.

AFAIK this is the only place.

(18 Jun '21, 07:36) Breck Carter
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×113
×30
×28

question asked: 18 Jun '21, 06:25

question was seen: 637 times

last updated: 18 Jun '21, 07:36