Is the vertica has the function that converts row to JSON format.?

HyeontaeJuHyeontaeJu Vertica Customer
edited March 2021 in General Discussion

Is the vertica has the function that converts row to JSON format.?

  • The table is not a flex table*
    example)
    select * from table;
    ** general result**
    id text
    1 amy
    2 john

if i use the json function
select json(*) from table;
** json result **

result
{'id':1, 'text':'amy'}
{'id':2, 'text':'john'}

Answers

  • SergeBSergeB - Select Field - Employee

    You can use the vmap functions to build a vmap for each row and then get its JSON representation.

     select maptoString(mapput(emptymap(),id,"text" using parameters keys=SetMapKeys('id','text'))) as json_string from foo;
                  json_string
    ---------------------------------------
     {
        "id": "1",
          "text": "amy"
    }
     {
        "id": "2",
        "text": "john"
    }
    
  • HyeontaeJuHyeontaeJu Vertica Customer

    Oh.. Thank you so much.. @SergeB
    I have one more question..
    If i need all column,, then, i have to write all column in the function??

  • SergeBSergeB - Select Field - Employee

    @HyeontaeJu Yes, you will need to list all the columns you wish to include in your JSON result.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file