Forum Stats

  • 3,824,920 Users
  • 2,260,440 Discussions
  • 7,896,347 Comments

Discussions

json path to get several rows. json docs can start with [ ?

juliojgs
juliojgs Member Posts: 596 Bronze Badge

Hi,

I'm exposing a POST service to insert rows. For example emp rows.

I get the data and do the insert of one row, easy, works.

Now the requirement is to receive a list of emps in json and do the inserts.

How would you code this?

If it were plsql collections I supose I could do a forall ... insert save exceptions, but I'm new to json.

Best Answer

  • cormaco
    cormaco Member Posts: 1,939 Silver Crown
    Answer ✓

    You almost got it right, just one small change:

    json_table(json_doc, '$[*]'  -- this matches all elements in the array
    
    ROW_NUMBER EXTERNAL_ID                                        INTERNAL_ID                                        DATE_READ                                          VALUE_READ
    ---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------
             1 A-2-CD-61-BOD---                                   A2LCB10AP001                                       01/01/2022 00:00                                       187956
             2 A-2-CE-31-ETR---                                                                                      01/01/2022 00:00                                        58423
             3 A-2-CE-31-MOT---                                                                                      01/01/2022 00:00                                        58423
             4 A-2-CE-33-ETR---                                                                                      01/01/2022 00:00                                        36778
             5 A-2-CE-33-MOT---                                                                                      01/01/2022 00:00                                        36778
             6 A-2-CE-34-ETR---                                                                                      01/01/2022 00:00                                        59324
             7 A-2-CE-34-MOT---                                                                                      01/01/2022 00:00                                        59324
             8 A-2-CE-35-ETR---                                                                                      01/01/2022 00:00                                        23472
             9 A-2-CE-35-MOT---                                                                                      01/01/2022 00:00                                        23472
            10 A-2-CE-36-ETR---                                                                                      01/01/2022 00:00                                        37900
            11 A-2-CE-36-MOT---                                                                                      01/01/2022 00:00                                        37900
            12 A-2-CN-12-VAP---                                   A2HLB30AN001                                       01/01/2022 00:00                                       279175
            13 A-2-CN-22-VAP---                                   A2HLB30AN002                                       01/01/2022 00:00                                       279212
            14 A-2-CN-31-MLN---                                   A2HFC10AJ001                                       01/01/2022 00:00                                       254154
            15 A-2-CN-32-MLN---                                   A2HFC20AJ001                                       01/01/2022 00:00                                       230091
    
    15 rows selected.
    


Answers

  • juliojgs
    juliojgs Member Posts: 596 Bronze Badge
    edited Jun 3, 2022 2:54PM

    I'm sampling the json I have to receive in the request. It would be something like this:

    [
    	{
    		"external_id": "A-2-CD-61-BOD---",
    		"internal_id": "A2LCB10AP001",
    		"date": "01/01/2022 00:00",
    		"value": 187956
    	},
    	{
    		"external_id": "A-2-CE-31-ETR---",
    		"date": "01/01/2022 00:00",
    		"value": 58423
    	},
    	{
    		"external_id": "A-2-CE-31-MOT---",
    		"date": "01/01/2022 00:00",
    		"value": 58423
    	},
    	{
    		"external_id": "A-2-CE-33-ETR---",
    		"date": "01/01/2022 00:00",
    		"value": 36778
    	},
    	{
    		"external_id": "A-2-CE-33-MOT---",
    		"date": "01/01/2022 00:00",
    		"value": 36778
    	},
    	{
    		"external_id": "A-2-CE-34-ETR---",
    		"date": "01/01/2022 00:00",
    		"value": 59324
    	},
    	{
    		"external_id": "A-2-CE-34-MOT---",
    		"date": "01/01/2022 00:00",
    		"value": 59324
    	},
    	{
    		"external_id": "A-2-CE-35-ETR---",
    		"date": "01/01/2022 00:00",
    		"value": 23472
    	},
    	{
    		"external_id": "A-2-CE-35-MOT---",
    		"date": "01/01/2022 00:00",
    		"value": 23472
    	},
    	{
    		"external_id": "A-2-CE-36-ETR---",
    		"date": "01/01/2022 00:00",
    		"value": 37900
    	},
    	{
    		"external_id": "A-2-CE-36-MOT---",
    		"date": "01/01/2022 00:00",
    		"value": 37900
    	},
    	{
    		"external_id": "A-2-CN-12-VAP---",
    		"internal_id": "A2HLB30AN001",
    		"date": "01/01/2022 00:00",
    		"value": 279175
    	},
    	{
    		"external_id": "A-2-CN-22-VAP---",
    		"internal_id": "A2HLB30AN002",
    		"date": "01/01/2022 00:00",
    		"value": 279212
    	},
    	{
    		"external_id": "A-2-CN-31-MLN---",
    		"internal_id": "A2HFC10AJ001",
    		"date": "01/01/2022 00:00",
    		"value": 254154
    	},
    	{
    		"external_id": "A-2-CN-32-MLN---",
    		"internal_id": "A2HFC20AJ001",
    		"date": "01/01/2022 00:00",
    		"value": 230091
    	}
    ]
    

    First question: Is it good design the json is starting with "[" ?

    I want to convert that in an insert sentence. Something like:

    insert into mytable (external_id, internal_id, date, value)

    select ...;

    So I first inserted the json in a clob column , and for the select part, I tried to convert it to rows with json_table function.

    select jt.*
    from test_json, 
    json_table(json_doc, '$'
    Columns (row_number FOR ORDINALITY,
         external_id  varchar2(50) PATH '$.external_id',
         internal_id    varchar2(50) PATH '$.internal_id',
         date_read     varchar2(50) PATH '$.date',
         value_read     number(10)  PATH '$.value')) as jt;
    

    And I'm getting no rows. I supose the PATH is wrong.

    Any idea?

  • cormaco
    cormaco Member Posts: 1,939 Silver Crown
    Answer ✓

    You almost got it right, just one small change:

    json_table(json_doc, '$[*]'  -- this matches all elements in the array
    
    ROW_NUMBER EXTERNAL_ID                                        INTERNAL_ID                                        DATE_READ                                          VALUE_READ
    ---------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------
             1 A-2-CD-61-BOD---                                   A2LCB10AP001                                       01/01/2022 00:00                                       187956
             2 A-2-CE-31-ETR---                                                                                      01/01/2022 00:00                                        58423
             3 A-2-CE-31-MOT---                                                                                      01/01/2022 00:00                                        58423
             4 A-2-CE-33-ETR---                                                                                      01/01/2022 00:00                                        36778
             5 A-2-CE-33-MOT---                                                                                      01/01/2022 00:00                                        36778
             6 A-2-CE-34-ETR---                                                                                      01/01/2022 00:00                                        59324
             7 A-2-CE-34-MOT---                                                                                      01/01/2022 00:00                                        59324
             8 A-2-CE-35-ETR---                                                                                      01/01/2022 00:00                                        23472
             9 A-2-CE-35-MOT---                                                                                      01/01/2022 00:00                                        23472
            10 A-2-CE-36-ETR---                                                                                      01/01/2022 00:00                                        37900
            11 A-2-CE-36-MOT---                                                                                      01/01/2022 00:00                                        37900
            12 A-2-CN-12-VAP---                                   A2HLB30AN001                                       01/01/2022 00:00                                       279175
            13 A-2-CN-22-VAP---                                   A2HLB30AN002                                       01/01/2022 00:00                                       279212
            14 A-2-CN-31-MLN---                                   A2HFC10AJ001                                       01/01/2022 00:00                                       254154
            15 A-2-CN-32-MLN---                                   A2HFC20AJ001                                       01/01/2022 00:00                                       230091
    
    15 rows selected.