Query JSON Dictionary Data in SQL

Query json dictionary data in SQL

Use JSON_TABLE and then UNPIVOT if you want the values in rows instead of columns:

SELECT *
FROM   (
  SELECT p.*
  FROM   table_name t
         CROSS JOIN
         JSON_TABLE(
           t.value,
           '$'
           COLUMNS (
             a PATH '$.a',
             b PATH '$.b',
             c PATH '$.c'
           )
         ) p
)
UNPIVOT ( value FOR key IN ( a, b, c ) );

So for some sample data:

CREATE TABLE table_name (
  value CLOB CONSTRAINT ensure_json CHECK (value IS JSON)
);

INSERT INTO table_name ( value ) VALUES ( '{"a":"value1", "b":"value2", "c":"value3"}' );

This outputs:

KEY | VALUE 
:-- | :-----
A   | value1
B   | value2
C   | value3

db<>fiddle here

---

If you want to do it dynamically then you can parse the JSON in PL/SQL and use GET_KEYS to get a collection of key names and then access the correct one by its position and correlate that to the value using FOR ORDINALITY:

CREATE FUNCTION get_key(
  pos  IN PLS_INTEGER,
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys( pos );
END get_key;
/

Then:

SELECT get_key( j.pos, t.value ) AS key,
       j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.value,
         '$.*'
         COLUMNS (
           pos   FOR ORDINALITY,
           value PATH '$'
         )
       ) j;

Outputs:

KEY | VALUE 
:-- | :-----
a   | value1
b   | value2
c   | value3

db<>fiddle here

Output json in dictionary (string-indexed list) notation from SQL Server

I don't think that you can generate JSON output with variable key names using FOR JSON AUTO or FOR JSON PATH, but if you can upgrade to SQL Server 2017, the following approach, that uses only JSON built-in support, is a possible option:

Table:

CREATE TABLE Data (
   Id varchar(2), 
   Customer varchar(50),
   Product varchar(50),   
   [Date] date,       
   [Count] int
)
INSERT INTO Data 
   (Id, Customer, Product, [Date], [Count])
VALUES   
   ('A1', 'Walmart', 'Widget', '20200101', 5),
   ('B2', 'Amazon',  'Thingy', '20200102', 10),
   ('C3', 'Target',  'Gadget', '20200201', 7)

Statement:

DECLARE @json nvarchar(max) = N'{}'   
SELECT @json = JSON_MODIFY(
   @json, 
   CONCAT(N'$."', ID, N'"'), 
   JSON_QUERY((SELECT Customer, Product, [Date], [Count] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER))
)
FROM Data

SELECT @json

Result:

{"A1":{"Customer":"Walmart","Product":"Widget","Date":"2020-01-01","Count":5},"B2":{"Customer":"Amazon","Product":"Thingy","Date":"2020-01-02","Count":10},"C3":{"Customer":"Target","Product":"Gadget","Date":"2020-02-01","Count":7}}

Notes:

Using a variable or expression instead of value for path parameter in JSON_MODIFY() is available in SQL Server 2017+. JSON_QUERY() is used to prevent the escaping of the special characters.

Insert a dictionary into SQL database created using JSON in Python

To insert the json into your database, don't convert it to a Python object; just store the string as read from the file.

with open(peripheralsJsonFile).read() as f:
    peripheralsInfo = f.read()

myquery = """INSERT INTO hw_info(hostname, peripheralsInfo) VALUES(%s,%s)""" 

cursor.execute(myquery, (hostname, peripheralsInfo))

When you want to use the data in your program later on you would pass the string from the SELECT query through json.loads() to make it a Python object.

EDIT

Two remarks here.

1. prefipheralsInfo = open(peripheralsJsonFile).read() does not close the file properly after reading, hence I used a context manager that takes care of that behind the scenes.

2. You format your query string by using Python's string interpolation. That makes your program vulnerable to SQL injection attacks. In my example I make use of a parameterized query to circumvent that vulnerability. Parameters also help making sure that your input is passed to your DB as the correct datatype.

---

Related Topics