logo
Tags down

shadow

How to join JSON with table?


By : user2174345
Date : October 18 2020, 08:10 AM
I hope this helps . I have a master table with column names for example: , Given a setup like this:
code :
declare @attributes table (Id int identity(1,1), Name sysname);

insert into @attributes (Name)
values
('Name'),('Email'),('Phone');

declare @data table (
  UserId int,
  JData nvarchar(max)
);

insert into @data (UserId, JData)
values
(1, N'{"Name":"abc","Email":"abc@test.com","Phone":"23231233","Company":"test"}'),
(2, N'{"Name":"xyz","Email":"xyz@test.com","Phone":"8909788","Location":"NA"}');
select d.UserId, ua.*
from @data d
  cross apply openjson(d.JData) ua
  inner join @attributes a on a.Name = ua.[key] collate database_default;


Share : facebook icon twitter icon

Using MySQL JSON field to join on a table


By : Niko - Estudio BL
Date : March 29 2020, 07:55 AM
may help you . I have a json field that stores a list of ids (not best practice here I know), I want to know if it's possible to use do operations on this JSON field and use them in the sql. , With the help of Feras's comment and some fiddling:
code :
  SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$')

SQL Join Table as JSON data


By : user2454840
Date : March 29 2020, 07:55 AM
wish helps you The reason you are getting multiple results is because of the one-to-many relationships between product_id and product_review and product_like causing duplication of rows prior to aggregation. To work around that, you need to perform the aggregation of those tables in subqueries and join the derived tables instead:
code :
SELECT "products".*, 
"pr"."reviews",
EXISTS(SELECT * FROM product_like lk
JOIN users u ON u.id = "lk"."user_id" WHERE u.id = 4
AND "lk"."product_id" = products.id) AS liked,
COALESCE("pl"."totalLikes", 0) AS totalLikes
FROM "products"
LEFT JOIN (SELECT product_id, array_to_json(array_agg("product_review".*)) AS reviews
           FROM "product_review"
           GROUP BY product_id) "pr" on "pr"."product_id" = "products"."id" 
LEFT JOIN (SELECT product_id, COUNT(*) AS "totalLikes"
           FROM "product_like"
           GROUP BY product_id) "pl" on "pl"."product_id" = "products"."id" 
id  title   image       reviews                                                                                                                     liked   totallikes
1   Photo   photo.jpg   [{"id":1,"product_id":1,"user_id":1,"review":"Great Product!"},{"id":2,"product_id":1,"user_id":2,"review":"Looks Great"}]  f       2
2   Book    book.jpg    [{"id":3,"product_id":2,"user_id":1,"review":"Could be better"}]                                                            f       2
3   Table   table.jpg                                                                                                                               f       0

How to make two table inner join with json


By : tonycollander
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , You have quite a few syntax and spelling errors, also you should read up more on how sql queries work with php. If your "cID" variable is user input, you should also sanitize this input before sending it to your database server to avoid sql injection attacks.
code :
$list = $db->query("SELECT*FROM table1 as u INNER JOIN table2 as p ON u.id = p.userID WHERE p.contentID = '$cID';");

$res = [];

if ($list ->num_rows > 0) {
 while ($row = $result->fetch_assoc()) {
  array.push($res, $row['response'])
 }
{

header("Content-type: application/json");
echo json_encode($res);
die();


How to join JSON to table (SQL Server)


By : user3243272
Date : March 29 2020, 07:55 AM
Does that help if you getting error 'Must declare the table variable "@json"', then you need to run the entire peace of code in one batch. Before you can join json string to the table, you need to parse it. Below is the example of how to do it. I did it on my machine and I'm getting values.
code :
declare @json nvarchar(max) 
set @json = 
'[{ 
"order":{ 
    "address":{ 
        "shipToAddLine":null, 
        "shipToCityStZip":null 
    }, 
    "items":[ 
        {"line":{ 
            "idItem":"DUR 82674", 
            "qty":"1" 
    }}]}}, { 
"order":{ 
    "address":{ 
        "shipToAddLine":null, 
        "shipToCityStZip":null 
    }, 
    "items":[ 
        {"line":{ 
        "idItem":"DUR 82674", 
        "qty":"1" 
}}]}} 
]'



select *
from someTable st join openjson(@json)
with(
itemId varchar(50) '$.order.items[0].line.idItem'
) as js
on st.itemid = js.itemID

Join records from another table as JSON array


By : user3712595
Date : March 29 2020, 07:55 AM
Any of those help We have the two tables: "product" and "product_part". One product can have many product parts, so that the product_part has id_product column that refernces a product's id column. , This would just be:
code :
SELECT product.name, json_agg(product_part.name) as parts
FROM product
JOIN product_part ON product.id = product_part.product_id
GROUP BY product.id, product.name;
Related Posts Related Posts :
  • SQLSERVER - How to list Procs created by a particular user?
  • SSIS Expression to get last day of the month given YYYYMM
  • how to sum up all time values on a given date?
  • Text Clause Explanation
  • SQL Server 2016 - Can't to log into SSMS after install
  • SQL Server table with different case in PowerShell SMO vs SSMS
  • Transpose and Create Variables
  • SQL : Output multiple left joined rows as one record for csv file
  • Removing Date From Date/Time Column
  • Subquery a Cast Function
  • Set based stacking update query
  • Can someone explain the `BypassPrepare` connection-parameter used when building an ETL via SSIS and VS?
  • What is the optimal way to get only latest ID's from table in SQL
  • Transforming a sql table into its inverse
  • Azure SQL Database Pricing
  • Log Reader error while adding a node in SQL Server peer to peer replication
  • Correct way to convert Month Name and Year to complete date SQL Server
  • Select records that appear twice, with a slight difference
  • tsql display value on first row of each group
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org